Setting a cell to be blank or a minimum value

[chrisdavies71]

Hi,

So the title of this thread is probably not the most helpful but I could not come up with anything better - suggestions are welcome.

I have a formula like this:

=IF(SUM(E9+H9+K9<=8),8,SUM(E9+H9+K9))

which basically says if the sum of the values in cells E9, H9 and K9 are less than 8 then the value of the cell where the formula is should be 8, or the sum of those cells if greater than 8

Question 1 - is there a better way of writing that?

The problem I have is that if there is no value in those cells (E9, H9, K9) the formula means I get a result of 8, which makes sense but I would like it to be blank.

Question 2 - is this possible?

Regards

Chris

 

[alansidman]

=if((e9 +h9+k9)=0,"",if(sum(e9+h9+k9<=8),8,sum(e9+h9+k9)))

 

[DanteAmor]

Try

=IF(AND(E9="",H9="",K9=""),"",IF(SUM(E9+H9+K9<=8),8,SUM(E9+H9+K9)))

 

[mikerickson]

=if(or(e9="", h9="", k9=""), "", max(8, e9+h9+k9))

 

[chrisdavies71]

=if((e9 +h9+k9)=0,"",if(sum(e9+h9+k9<=8),8,sum(e9+h9+k9)))

Thanks for this Alan. A slight tweak to this got exactly what I needed. You are a star.

Regards

Chris

 

[alansidman]

Thanks for the feedback.

 

[Peter_SSs]

In answer to your first question

=IF(SUM(E9+H9+K9<=8),8,SUM(E9+H9+K9))

Question 1 - is there a better way of writing that?

=MAX(SUM(E9,H9,K9),8)

A slight tweak to this got exactly what I needed.

I'm not sure what 'tweak' you gave it but assume that it was correcting where you originally mis-placed a closing parenthesis in your first post formula.
However, since you were asking about 'better ways of writing', note that the red parts of this formula are redundant & can be omitted since the blue parts already calculates the sum
=if((e9 +h9+k9)=0,"",if(sum(e9+h9+k9)<=8,8,sum(e9+h9+k9)))

Note also that Alan's formula does not actually test for empty cells in E9,H9 & K9. For example, if those cells held the following values, the suggested formula would return "" when all 3 cells do contain values.
E9: 4
H9: 5
K9: -9



Whilst you already have something that apparently 'works', here are my suggestions.

If you want the formula cell to be blank if all 3 cells are empty then
=IF(COUNT(E9,H9,K9),MAX(SUM(E9,H9,K9),8),"")

If you want the formula cell to be blank if any of the 3 cells is empty
=IF(COUNT(E9,H9,K9)=3,MAX(SUM(E9,H9,K9),8),"")