problem with decimal in calculation

[Amanda]

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

 

[JE McGimpsey]

One way:

Format/Cells/Number/Custom 0A;0B;0_A

with the formula: =IF(G8<>0,ABS(E8/G8-1)*100,"N/A")

 

[ERR229]

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]

Remove the references to A and B in the formula, then create a custom format
(Formt>Cells>Number 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.

 

[Gary''s Student]

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")

 

[Arvi Laanemets]

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