Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
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!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
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. "JSR929" wrote: 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!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
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 DTHIS web: www.nickhodge.co.uk "JSR929" wrote in message ... 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!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
Try this in A3: =IF(A20,A1-A2,"")
Of course if you enter 181 in A2, you will weigh -1 pounds. Good luck. Tyro "JSR929" wrote in message ... 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!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
Sorry, misread, you wanted 0, not a blank
=IF(A20,A1-A2,0) "JSR929" wrote in message ... 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!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
Formula in F11:
=(B7-D11)/B7 Format as PERCENTAGE -- Biff Microsoft Excel MVP wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
=(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. -- David Biddulph wrote in message ... 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Biggest Loser Formula
"Tyro" wrote...
Sorry, misread, you wanted 0, not a blank =IF(A20,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 "", A20 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function? Biggest distance to the Nearest city ... | Excel Discussion (Misc queries) | |||
Which function? Biggest distance to the Nearest city ... | Excel Worksheet Functions | |||
Finding state that contains employees biggest sale | Excel Discussion (Misc queries) | |||
Finding a name with biggest number | Excel Worksheet Functions | |||
Would u help me with a Biggest Movers type of comparison? | Excel Discussion (Misc queries) |