Incorrect formula

[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)

 

[Mel]

I have also tried

=((K24+K25)*3)+(K26+K27)+(K36*2)+IF(OR(K28>0,K27>0),(K28+1)+(K27+1),0)

 

[Gary''s Student]

Rather than:
+(L29+1)
try:
+IF(L29=0,0,L29+1)


same for L28

 

[Mel]

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.

 

[Sandy Mann]

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

 

[Mel]

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

 

[Sandy Mann]

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

 

[Mel]

Thank you Sandy

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

 

[ShaneDevenshire]

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.