Formula Qn. from a Newbie

  • Thread starter Thread starter Fred J
  • Start date Start date
F

Fred J

I recently saw this formula in an example:-

[in cell A2] =A1 + (B1>=100)

does this mean that A2 will equal the sum of A1 & B1 if B1 is equal to or
greater than 100 ; otherwise A2 will equal A1 ?

If so, is there anywhere that little gems like this are documented ? - I
browsed through the help pages but found nothing.

TIA
Fred
 
Hi Fred

never seen the formula before, so i stuck it in a workbook and discovered
that
if in A1 i have 5
and in B1 i have 5
then in A2 i will have a total of 5
however, if i change B1 to 100
then A2 changes to 6

the reason is that the (B1>=100) is a test ... if it's true it returns 1, if
its false it returns 0 ...
so in my first example the formula in A2 is =5+(5>=100) ... =5+(false) ....
= 5 + 0... = 5
in my second example the formula in A2 is =5+(100>=100) ... =5+(true) ....
=5+1... = 6

why you would ever need to use a formula like this is another question
entirely.

However, coming back to your other question about a resource that lists
things like this ... my advice is hang around these newsgroups and you'll
come across some really amazing formulas. You could also have a look at
John Walkenbach's "Excel 200x Formulas" book ...and check out some of the
great excel websites around ... my two favourite ones are Debra Dalgleish's
(www.contextures.com/tiptech.html) & Chip Pearson's (www.cpearson.com) ...
but you can find more listed at www.mvps.org/links.html or do a search at
groups.google.com for excel website recommendations (Norman Harker has
posted an extensive list in the (recent) past).


Cheers
JulieD
 
Hi
the expression
(B1>=100)
will either return TRUE or FALSE. If you use a boolean expression
within a mathematical operation (as in this formula by adding it to a
cell value) Excel will coerce the boolean value to a numerical value
(TRUE=1/FALSE=0)

So the formula
=A1 + (B1>=100)
will return the following:
1. If B1>=100:
=A1+(TRUE) = A1+1

2.
1. If B1<100:
=A1+(FALSE) = A1+0 = A1

If you want the sum of A1 and B1 only if B1>=100 (and other wise only
A1) you may use:
=A1 + (B1>=100)*B1
 
Got it - many thanks (tks also to Frank Kabel)

As to the need for such a formula it was with reference to a sequential
counter e.g. when Col B reaches a certain value Col A must increment.
It's only now that I've noticed that these increments will _always_ be by a
value of 1.

Fred


JulieD said:
Hi Fred

never seen the formula before, so i stuck it in a workbook and discovered
that
if in A1 i have 5
and in B1 i have 5
then in A2 i will have a total of 5
however, if i change B1 to 100
then A2 changes to 6

the reason is that the (B1>=100) is a test ... if it's true it returns 1, if
its false it returns 0 ...
so in my first example the formula in A2 is =5+(5>=100) ... =5+(false) .....
= 5 + 0... = 5
in my second example the formula in A2 is =5+(100>=100) ... =5+(true) ....
=5+1... = 6

why you would ever need to use a formula like this is another question
entirely.

However, coming back to your other question about a resource that lists
things like this ... my advice is hang around these newsgroups and you'll
come across some really amazing formulas. You could also have a look at
John Walkenbach's "Excel 200x Formulas" book ...and check out some of the
great excel websites around ... my two favourite ones are Debra Dalgleish's
(www.contextures.com/tiptech.html) & Chip Pearson's (www.cpearson.com) ...
but you can find more listed at www.mvps.org/links.html or do a search at
groups.google.com for excel website recommendations (Norman Harker has
posted an extensive list in the (recent) past).


Cheers
JulieD


Fred J said:
I recently saw this formula in an example:-

[in cell A2] =A1 + (B1>=100)

does this mean that A2 will equal the sum of A1 & B1 if B1 is equal to or
greater than 100 ; otherwise A2 will equal A1 ?

If so, is there anywhere that little gems like this are documented ? - I
browsed through the help pages but found nothing.

TIA
Fred
Click to expand...
Click to expand...
 
Back
Top