Want it show 0??

[toonmadfan]

Hi all

just after a quick fix (and probably a very simple one too, lol)

I want a cell to display 0 if a different cell is less than a given
value (in this case 95). One of the issues i face is that the cell i
want to display as 0 (in the above instance) is needing a calculation
in there.

b4 = the cell which will determine the outcome, less than 95=0 in cell
b10
b5 = value
b9 = value
b10 = b5+b9 currently (but should display 0 if b4 is less than 0)

All help appreciated

yY

 

[Tom Hutchins]

In B10, try

=IF(B4<95,0,B5+B9)

Hope this helps,

Hutch

 

[RagDyeR]

=(B4>=95)*(B5+B9)
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi all

just after a quick fix (and probably a very simple one too, lol)

I want a cell to display 0 if a different cell is less than a given
value (in this case 95). One of the issues i face is that the cell i
want to display as 0 (in the above instance) is needing a calculation
in there.

b4 = the cell which will determine the outcome, less than 95=0 in cell
b10
b5 = value
b9 = value
b10 = b5+b9 currently (but should display 0 if b4 is less than 0)

All help appreciated

yY

 

[yopyop]

Tom

thanks for your answer there. Got the same answer after double
posting due to not being able to locate original post but thanks in
any case for your time

RD - your formula doesn't work for me? Again, thanks for your time.

yY

 

[RagDyer]

Thanks for feeding back.

I'm very curious though.

Can you tell me in what way the formula I suggested didn't work?

 

[yopyop]

Hi RD

it just returned a 0 regardless of what was entered into b4? I can
follow the logic of the other suggestion (remember i'm a noob, but i
can't follow yours. If you could explain further to help me
understand why yours should work, i may be able to answer?

Thanks again for the help though

yY

 

[Ragdyer]

In a new, blank sheet, enter 95 into B4.

In say D4 enter:
=B4>=95

Since the formula is declaring, in English terms, that B4 is greater then or
equal to 95, you'll get a return of TRUE.

Change B4 to say 94, and you'll see D4 change to FALSE.

XL treats TRUE as 1 and FALSE as 0.

This conversion to numeric values can be brought about in any number of
different situations involving arithmetic functions, such as:

=(B4>=95)*1
=(B4>=95+0
=(B4>=95-0
=--(B4>=95

See what happens to these formulas as you change the value in B4 to be
above, at, or below 95.

NOW, from basic math,
multiply a value by 1, and you get the value,
multiply a value by 0 and you get 0.

So, my suggested formula:
=(B4>=95)*(B5+B9)
is multiplying the sum of B5 and B9 by either 1 or 0,
depending on the value in B4.

 

[RagDyeR]

Sorry!
Seems most of my example formulas are missing the closing parens:

=(B4>=95)*1
=(B4>=95)+0
=(B4>=95)-0
=--(B4>=95)

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


In a new, blank sheet, enter 95 into B4.

In say D4 enter:
=B4>=95

Since the formula is declaring, in English terms, that B4 is greater then or
equal to 95, you'll get a return of TRUE.

Change B4 to say 94, and you'll see D4 change to FALSE.

XL treats TRUE as 1 and FALSE as 0.

This conversion to numeric values can be brought about in any number of
different situations involving arithmetic functions, such as:

=(B4>=95)*1
=(B4>=95+0
=(B4>=95-0
=--(B4>=95

See what happens to these formulas as you change the value in B4 to be
above, at, or below 95.

NOW, from basic math,
multiply a value by 1, and you get the value,
multiply a value by 0 and you get 0.

So, my suggested formula:
=(B4>=95)*(B5+B9)
is multiplying the sum of B5 and B9 by either 1 or 0,
depending on the value in B4.