Setting a cell to be blank or a minimum value

chrisdavies71

New Member
Joined
Oct 20, 2019
Messages
8
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try

=IF(AND(E9="",H9="",K9=""),"",IF(SUM(E9+H9+K9<=8),8,SUM(E9+H9+K9)))
 
Upvote 0
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),"")
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top