Need to create a formula that allows me to do the following:
1. Add 5 numbers, then subtract the max of those numbers. {I can do this already}
2. Only compute value in #1 if the number of entries is greater than 3.
Here are examples of what I want to do:
A. 2+3+4+5+999 = 14
B. 2+4+6+8+3 = 15
C. 2+ +5+9+ = [creates blank space]
D. 4+ + + +. = [creates blank space]
—–
Kazoo2U
Are these cells? So in example A, are these say cells B2 through F2? So the formula you have now is in G2 and it is =B2+C2+D2+E2+F2?
If that is the case, then just use ISBLANK to determine if a cell is empty. You can add those together to get the number of empty cells. Then use an IF statement to test to see if there are more than 2 blank cells and put a blank into the result. Otherwise, sum up the five cells.
=IF(ISBLANK(B2)+ISBLANK(C2)+ISBLANK(D2)+ISBLANK(E2)+ISBLANK(F2)>2,"",SUM(B2:F2))
Your formula works! Thanks for the quick response.
My cells are in a column, but I figured out how to insert the new cells into your formula.
Once I have all 5 entries added together, now have to figure how to subtract the max entry to get the best 4 of 5 values.
Kazoo2U: Just use the MAX function to get that. So -MAX(B2:F2) after the SUM.
Again, thanks for the quick response.
After a little fiddling, your formula works.
Among the best, if not the best, customer support on the web.