Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
when using the below formula, the answer will not follow the decimal format I
have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*100)&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
One way:
Format/Cells/Number/Custom 0A;0B;0_A with the formula: =IF(G8<0,ABS(E8/G8-1)*100,"N/A") In article , Amanda wrote: when using the below formula, the answer will not follow the decimal format I have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*100 )&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
Hi again,
I misread your desired format, try this instead of what I gave you in the previous reply: #,##0"A";#,###"B" Sorry for the confusion! -- ERR229 "Amanda" wrote: when using the below formula, the answer will not follow the decimal format I have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*100)&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
Remove the references to A and B in the formula, then create a custom format
(FormtCellsNumber then choose Custom) to add the A if positive and B if negative. Then apply that format to the range of cells containing your formula. Here's an example of a format that might work for you. "A "#,##0.00;"B "#,###.00 Hope that helps. -- ERR229 "Amanda" wrote: when using the below formula, the answer will not follow the decimal format I have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*100)&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
Modify your formula to use INT():
=IF((INT((E8-G8)/ABS(G8)*100))=0,INT(((E8-G8)/ABS(G8)*100))&"A",INT(ABS((E8-G8)/ABS(G8)*100))&"B") -- Gary's Student "Amanda" wrote: when using the below formula, the answer will not follow the decimal format I have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*100)&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
problem with decimal in calculation
Hi
Applying numeric, date, etc. format to cell with text value (you formula does return a text string!) doesn't affect how this value is displayed. You or use the formula =(E8-G8)/ABS(G8)*100 , which returns a numeric value, and format the cell with result like this: Custom "#.0A;#.0B" Or use the formula like this =TEXT(ABS((E8-G8)/G8)*100,"#.0") & IF(E8<G8,"B","A") , which returns a string value. Arvi Laanemets "Amanda" wrote in message ... when using the below formula, the answer will not follow the decimal format I have assigned under format. If I leave off the &"A" and the &"B" then it works fine by using the decimal format I selected, however with the &"A" it gives me a decimal answer 0.0000000000 =IF(((E8-G8)/ABS(G8)*100)=0,((E8-G8)/ABS(G8)*100)&"A",ABS((E8-G8)/ABS(G8)*1 00)&"B") What I need is if it is a positive result, then the cell will hold the result with an A behind it and if it is negative, then the result with a B....but I only want 1 number(no decimals), i.e. (10-5)/ABS(5) * 100 equals 200 which tells me it is a 200% increase over last year....in the cell I want the answer to read 200A (without the % sign which is why I multiplied by 100). Thanks in advance for your assistance! Amanda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save Link Problem | Links and Linking in Excel | |||
Formula for: Format Decimal places? | Excel Discussion (Misc queries) | |||
h:mm to decimal value | Excel Discussion (Misc queries) | |||
Change Dots into Commas as Decimal Divider | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) |