Want it show 0??

  • Thread starter Thread starter toonmadfan
  • Start date Start date
T

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
 
=(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
 
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
 
Thanks for feeding back.

I'm very curious though.

Can you tell me in what way the formula I suggested didn't work?
 
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
 
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.
 
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.
 
Back
Top