Clean up If/Then formula, adding in "isblank" range

I have written a formula that almost works perfectly... but 1. I am not sure if this is the best way to go about it, and 2. Almost.

Please Login or Register  to view this content.

Cells D16:F16 are sometimes blank..... so if they are blank, and C16>70.... then 10.50 should still be returned
If they are not blank, well then the above formula still stands, well, that is if that is the best way to acomplish this?

Column C is Weight
D:F is Length, Width, Height

If C is over 70lbs, OR the longest side is over 60, or second longest side is over 30, then return 10.50, Else return 0.


Thanks in advance.

If the range(D16:F16) is blank, and weight(C16) is under 70, then yes, return zero.
If the range is blank and weight is over 70, then still return 10.50

Seems to work perfect. Thank you! Is Median the best way to accomplish this?

Basically i need to look at the largest number, and the second largest number that would occur in columns D:F. The user may not enter those numbers in order..... since there were only 3 numbers to look at, that is why I went with Median....

Thank you for the input..... for now, it does seem that Max / Median is working... but i did find one bug.... do you think that "large" would fix it?

Attached is a copy of the workbook so that you can see the real data on this.

So if data appears ONLY in one of the cells D:F, the Max/Median calculation is pulling the data from that cell... instead it should ONLY complete the calculation if all 3 cells in D:F have data in them. Otherwise the data it pulls is not correct.

I tried playing with the IfError statement that you suggested for the cells in column J as well.... but I was not able to get it to work.

It almost works correctly with the If/Then statement in Column J, but it produces an error when D:F is blank. Would you mind providing help on this as well?

Try

=IF(SUM(D9:F9)=0,0,IF(SUM(MAX(D9:F9),(MEDIAN(D9:F9)*2),(MIN(D9:F9)*2))>130,67.5,0))

Thank you! that does get rid of the error displayed when D:F is empty.

However, If there is data only in say D9, and not in E9 and F9, it is calculating the Max/Median still. Whereas it should only calculate if all 3 are filled.

i tried "Product" instead of "Sum" thinking that if not all three cells D:F had numbers in it that a product would return zero... but it does not.

If number "5" is a number say in cell D9, E9 and F9 are blank.... Product(D9:F9) will return "5"

This checks if D:F is all cells have data and returns ) if false

=IF(COUNTA(D6:F6)<>3,0,IF(SUM(MAX(D6:F6),(MEDIAN(D6:F6)*2),(MIN(D6:F6)*2))>130,67.5,0))

PERFECT!! Thank you!