Incorrect formula

  • Thread starter Thread starter Mel
  • Start date Start date
M

Mel

Would appreciate help with my formula. I only want the 1 to be added to L28
and L29 if they have a value greater than 0. this formula produces 2 even if
all the cells have nothing in them, but I need there to be nothing in the
cell if no values are in any cells.



If the value of every cell in the formula was 1 then my answer should be 14
but if cell L28 is 0 then the answer should be 12, and if neither L28 or L29
has a value then the answer should be 10, but because of the =1 in my
formula I get an answer that is 12.(or 2 greater than I want if values are
in other cells)



Help appreciated.



=((L24+L25)*3)+(L26+L27)+(L36*2)+(L28+1)+(L29+1)
 
I tried

=((K24+K25)*3)+(K26+K27)+(K36*2)+IF(K28=0,0,K28+1)+IF(K27=0,0,K27+1) but
this still gives me a value of 2 high if K28 and K27 are zero.
 
Try:

=((L24+L25)*3)+(L26+L27)+(L36*2)+(L28+(L28>0))+(L29+(L29>0))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks Sandy, that works.

In my limited experience though, how does the "one" get added to the L28 in
this formula? It certainly does it.

Mel
 
Hi Mel,

(L29>0)

evaluates to either TRUE or FALSE. When a TRUE/FALSE Boolean is used in
arithmetic, XL converts a TRUE to 1 or a FALSE to zero.

(L29+(L29>0))

Therefore becomes (L29 + ( 1 )) or (L29 + ( 0 )) as the case may be, thus
adds on either one or zero.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thank you Sandy

Sandy Mann said:
Hi Mel,

(L29>0)

evaluates to either TRUE or FALSE. When a TRUE/FALSE Boolean is used in
arithmetic, XL converts a TRUE to 1 or a FALSE to zero.

(L29+(L29>0))

Therefore becomes (L29 + ( 1 )) or (L29 + ( 0 )) as the case may be, thus
adds on either one or zero.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Hi,

And a little shorter still:

=(L24+L25)*3+SUM(L26:L29)+L36*2+(L28>0)+(L29>0)

If this helps, please click the Yes button.
 
Back
Top