Great, the =IF(ISNUMBER(A1);A1-3;"") The following is a summary of what worked;
number one =IF(A1=0;””;A1) if A1 is empty get empty. If A1 has a 0 get an empty cell. If there is a number that is not 0 get that number. Number two =IF(ISBLANK(A1);””;A1) and =IF(ISNUMBER(A1);A1;””) produce the same following results if A1 is empty get empty. If A1 has a 0 get a 0. If there is a number that is not 0 get that number. Number three =IF(ISNUMBER(A1);A1-3;””) does the following; If A1 is empty then B1 is empty. If A1 has any number in it, that number in A1 is reduced by 3. If there is 0 in A1 then B1 would be -3. If there is a 4 in A1 then B1 would be 1. If there is a 3 in A1 then B1 would be 0. That is the good new. The bad news is that I have another question. In both A1 and B1 there is a percent that can be either a positive or a negative percent. (for example it could be -10% or 10%) In C1 there should be the percentage by which A1 is greater or less than B1. (for example if both A1 and B1 is 10%, the difference is 0%. If A1 is 10% and B1 is -10%, the difrerence is 200%) When either =(A1-B1)/A1 or =(-A1_B1)/A1 is in C1 there is a problem. In three out of four combinations either formula works. It works when a positive number is in both A1 and B1. It works when a negative number is in both A1 and B1 The formula does not work in one of the four cominations when there is a negative number in A1 and a postive number in B1, The formula does not work in one of the four cominations when there is a positive number in A1 and a negative number in B1. Is there an IF to correct this or is there some other way to get the % or the ratiio that measurs how much A1 is greater than or less than B1 On Sun, Mar 8, 2009 at 5:55 PM, Brian Barker <[email protected]>wrote: > At 15:41 08/03/2009 -0600, Walter Hildebrandt wrote: > >> an additional IF is needed in the spreadsheet. (I think this will be the >> last IF needed request) >> > > Phew! > > If A1 is empty then B1 should be empty >> If A1 has any number in it, that number in A1 is reduced by 3. If there >> is 0 in A1 then B1 would be -3. If there is a 4 in A1 then B1 would be 1. >> If there is a 3 in A1 then B1 would be 0. >> > > This is called subtraction. Try: > =IF(ISBLANK(A1);"";A1-3) > or: > =IF(ISNUMBER(A1);A1-3;"") > > At 22:56 08/03/2009 +0100, Franz Wein wrote: > >> write in B1: *= if(A1="";A1;A1-3)* >> > > Actually, this doesn't work: if A is empty it gives zero for B, not the > empty cell required. You could change this slightly to: > =IF(A1="";"";A1-3) > > At 18:35 08/03/2009 -0400, Gene Young wrote: > >> My original formula still stands: >> =IF(A1;A1-3;"") >> > > This also doesn't work: it fails to distinguish zero and empty in A, so > zero in A gives an empty cell for B, not the -3 required. > > > I trust this helps. > > Brian Barker > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
