MIN and MAX on conditions.. loosing hair

[dallenk]

Hey everyone.. I've gotten a ton of help from reading posts here, but I can't seem to figure this one out. I have built a spreadsheet where I can enter quotes from several vendors and it will calculate the best pricing.. but I can't seem to figure out how to get two values based on a couple conditions.

1 - I needed to get the min price including zero values.. done easily with =MIN(F12:F15) no problem there.. but then i found i needed to account for the situation when I don't have all four of the vendors filled in.. maybe I only obtained 3 quotes instead of 4.. this throws off my numbers.

So is it possible to only look for the min value IF the vendor name is also filled in? I've been looking at the new(ish) minifs functions, or trying to see if I can use a vlookup..


1st screenshot shows it working fine as long as all 4 vendors are used.


-- removed inline image ---






this is where I am stumped. if I don't have vendor4 filled in, how can I get excel to ignore the cell in the range?



As soon as I saw that, I also see the HI/LOW spread on the price differences are also not working right.
I'm sure once I can see how to ignore a cell in the min, I'll be able to fix the HI/LOW spread as well.

Thanks !!

 

[dallenk]

Ok that's strange.. i can't find a way to edit my post.. apparently I can't paste screenshots so I'll link to them..

 

[Guru008]

Hi,

Try this
=MIN(IF(I3:I8<>"",J3:J8)) after entering the formula press Ctrl+Enter

This is an array formula after pressing Ctrl+shift it will look like {=MIN(IF(I3:I8<>"",J3:J8))}

 

[dallenk]

Holy crap,, it was that simple? I was trying to way over-complicate it.
Thank you very much.