2.5-0.89¢-#VALUE!-2.5 - =IF((F3/H3)>0,TEXT(F3/H3,"$0.00") No work

A

atb

cells at are:
2.5 = f3 / 0.89 = H3.. #value! should be: 0.36

=IF((F3/H3)>0.99,TEXT(F3/H3,"$0.00"),TEXT(F3/H3,"0.00¢"))

On another line with same formula: this works

8.3 $1.39 0.17¢

The only thing I can tell is that the second column is not less then $1.00

Please help me with this... thank you.
 
B

Barb Reinhardt

Is it possible that you have something else in F3 or H3 besides numeric
values? Also, if the ratio is < 0.99, do you really want to have it listed
as 0.00 CENTS? Your result will be 2 orders of magnitude smaller than what
it is.
 
J

Jim Thomlinson

Why not just use a custom format (Right Click -> Format Cell -> Number ->
Custom) for the cell something like this...

[>0.99] $0.00; 0.00¢

Now just use the formula F3/H3 in the cell and it will format the way you
intend... The only difference being that your formula is returning text and
not a number but I should think you want the value to be a number anyways.
 
A

atb

atb said:
cells at are:
2.5 = f3 / 0.89 = H3.. #value! should be: 0.36

=IF((F3/H3)>0.99,TEXT(F3/H3,"$0.00"),TEXT(F3/H3,"0.00¢"))

On another line with same formula: this works

8.3 $1.39 0.17¢

The only thing I can tell is that the second column is not less then $1.00

Please help me with this... thank you.

*******

Figured it out, the problem is: I was using the Text converted version to
do my calculations instead of the intger version of it. dividing f3 as text
... Doh
 
A

atb

Jim Thomlinson said:
Why not just use a custom format (Right Click -> Format Cell -> Number ->
Custom) for the cell something like this...

[>0.99] $0.00; 0.00¢

Now just use the formula F3/H3 in the cell and it will format the way you
intend... The only difference being that your formula is returning text and
not a number but I should think you want the value to be a number anyways.

Jim, that's cause I'm not smart enough to figure that out yet..:)

However, its cleaner, and I do want the Dollar symbols to be added as text
because its not imported into a package I have to do labels...
 
D

David Biddulph

I assume that you've got the F3 & H3 back to front in your question? If
you're expecting F3/H3 to be 0.36, then the 0.89 must be in F3 and the 2.5
in H3. With those numbers, your formula gives 0.36¢.

If you're getting #VALUE as an answer, then I assume that one or both of the
numbers isn't actually a number, but text. Change it to a number.
 

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