Biggest Loser Formula

[JSR929]

Ok, I'm going crazy here! I know I've done this before years ago but for the
life of me can't figure it out. Ok, lets see if I can explain this clearly
enough so you can understand...

Lets say in Cell A1 I have the number 180. In cell A2 there is nothing. In
cell A3 I have this Formula =A1-A2. After making that formula, in cell A3
appears 180. I want cell A3 to reflect 0 until I put a number in cell A2 to
subtract.

Here's what we're doing... in our office, we are having our own competition
as to who can lose the most weight/percentage of weight in 8 weeks. I have a
spreadsheet to track all the changes, however, the 180 is the starting weight
and in cell A2 is the following weeks weight. A3 is the difference, however
in A3, it still displays 180 until I actually put in a new weight in A2.

Does anyone know how to have A3 reflect "0" until it can subtract A1 & A2?

Thanks!!!

 

[Daniel Trojan]

Use this IF formula in the "A3" range:

=IF(A2<>"",A1-A2,0)

Translation: if A2 has a value, calculate the difference. Otherwise,
display 0.

 

[Nick Hodge]

Use an IF statement (and possibly an OR to handle if either are empty

=IF(OR(A1="",A2=""),0,A1-A2)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

 

[Tyro]

Try this in A3: =IF(A2>0,A1-A2,"")
Of course if you enter 181 in A2, you will weigh -1 pounds. Good luck.

Tyro

 

[Tyro]

Sorry, misread, you wanted 0, not a blank

=IF(A2>0,A1-A2,0)

 

[ckshowtime]

I am doing the same thing for our office...however I want each person
to have this spreadsheet so they can see their own % of change each
week. cell B7 shows their initial weight, D11 shows that weeks
weight, and F11 would show percent change.

It is not converting the way I would like so I know I am missing a
step. If I weigh 188 inititally then 184 at next weight in I want it
to show what percent that 4 lbs of weight loss would be.

 

[T. Valko]

Formula in F11:

=(B7-D11)/B7

Format as PERCENTAGE

 

[David Biddulph]

=(B7-D11)/B7 or =1-D11/B7 formatted, in either case, as a percentage, would
give you a 2.13% loss.

It is always useful, when you are asking such a question, if you tell us
what formula you are using, what result it gives for the given inputs, and
what result you expected. That way we can tell you what the problem was
with your formula.

 

[Harlan Grove]

Sorry, misread, you wanted 0, not a blank

=IF(A2>0,A1-A2,0)

....

A classic Excel bug in the making!

While cell A2 would be positive when it contains a number (since it'd
contain weights), there's an off chance someone could enter a space or
just an apostrophe in A2. In either case, A2 would appear blank but
would actually be a string and not 'blank' in Excel's ISBLANK sense.
Unless one's using Transition Formula Evaluation, if A2 contained just
an apostrophe, so evaluated as the zero length string "", A2>0 would
be TRUE, and A1-A2 would return #VALUE!.

ALL strings are greater than ALL numbers in standard Excel formula
evaluation, so it's usually a good idea to ensure consistent types
when comparing cells to constants, e.g., better to use

=IF(N(A2)>0,A1-A2,0)

Safer still would be

=IF(COUNTIF(A1:A2,">0")=2,A1-A2,0)