Ignore Blank Cells

[djaldente]

Hi,

Can someone help me with this formula please.. If any of the I cells (I18, I19, I20) are blank I need them not to be counted.

=(C13/(C13/I18+C13/I19+C13/I20)*K6*K18)+(C13/(C13/I18+C13/I19+C13/I20)*K6*K19)+(C13/(C13/I18+C13/I19+C13/I20)*K6*K20)

Cheers

 

[pgc01]

Hi

I don't understand this formula.

First thing I don't understand is what C13 is doing

C13/(C13/I18+C13/I19+C13/I20)

makes no sense to me. If you divide numerator and denominator by C13 you get

1/(1/I18+1/I19+1/I20)

So what is C13 doing there?

 

[inactiveuserps07]

@djaldente, your main formula can be shortened quite a bit (maybe even further than I have in a couple minutes here). That said, the following should work for your purposes:

=C13/(C13/IF(I18>0,I18,1)+C13/IF(I19>0,I19,1)+C13/IF(I20>0,I20,1))*K6*(K18+K19+K20)

 

[djaldente]

Hey mate,

thanks for responding, sorry for not being clear enough.. the value of C13 is 2360 - so i have:
2360/(2360/13.25+2360/20.70+2360/66.25)*9.5*90
plus
2360/(2360/13.25+2360/20.70+2360/66.25)*9.5*95
plus
2360/(2360/13.25+2360/20.70+2360/66.25)*9.5*100

if one of the 3 figures/cells in brackets is blank i dont want it counted. at the moment i just get an error message.

Thanks again for your help mate

Charlie

 

[inactiveuserps07]

Charlie, you've got two different people corresponding with you here. Did you try the formula I left for you above?

 

[Scott Huish]

Your formula can for sure be shortened to this. As has already been mentioned the use of cell C13 is irrelevant because the division evaluates to 1 in the formula.

=1/(1/IF(I18>0,I18,1)+1/IF(I19>0,I19,1)+1/IF(I20>0,I20,1))*K6*(K18+K19+K20)

 

[djaldente]

Charlie, you've got two different people corresponding with you here. Did you try the formula I left for you above?

Hi Erik,

Yea mate its working thank you very much, the figures are a bit off though.. im just going through it now.

Thanks heaps mate

 

[djaldente]

Hi Erik,

Thanks for the help mate.. so in your formula "C13/IF(I18>0,I18,1)" returns the value in I18 if its not blank - i wish to divide C13 by this value..

Cheers

 

[inactiveuserps07]

Charlie, try this:

=IFERROR(C13/(IF(I18>0,C13/I18,0)+IF(I19>0,C13/I19,0)+IF(I20>0,C13/I20,0)),1)*K6*(K18+K19+K20)

 

[djaldente]

That one works like a charm..

Cheers mate