Results no longer currency when adding text to formula / formulas based on resulting cell don't work

S

StargateFan

A cell formatted for currency that has this formula:
=IF($A$2<>"",($E$2) & " x 2 = " & CEILING(($E$2*2),0.25),"")

now gives the result of: 8.4 x 2 = 17
instead of: $8.40 x 2 = $17.00

And E4 which would then use the above result of $17.00 now gives me a
#value return.

Is there a way to use the text and formula while still retaining
currency formatting and yet XL2K still can work with results?

TIA. I got the text and formula approach above from the archives.
 
A

Andrew Taylor

The cell formatting will only take effect if the result of
the calculation is a number, so you need to format
each part of your equation:

=IF($A$2<>"",TEXT($E$2,"$0.00") & " x 2 = " &
TEXT(CEILING(($E$2*2),0.25),"$0.00"),"")

You can't get E4 to "use the result" of this in a calculation, because
it's text. I would just use CEILING(($E$2*2),0.25) instead in the
formula in E4 instead.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top