percent and dollar formula don't mix

[Wanna Learn]

Hello in Excel 2003 my formula is =(V10-U10/X10)/V10) this means
current list price - fixed price /by units sold / by current list price.
example current list price is $257,506.00 fixed price is $225,000.00 units
sold is 1 so the discount is 12.62% my problem is if there is more than 1
unit sold I get the wrong answer also when the fixed price is not a dollar
amount but a percent example 10% then my answer is also wrong. How do I
correct his thanks a million.

 

[Brad]

It would be helpful if you provide what the correct answer would be - however
you may want to try this
=((V10-U10)/X10)/V10) (if x10 = 2 in your example the amount you should get
would be 6.31%

Not sure how to address the percentage problem, need more clarification.

 

[joeu2004]

The percentage discount has nothing to do with number of units sold --
unless "fixed price" is a total of all units sold, whereas "list
price" is per-unit (surprise!). (If that were the case, your original
formula is correct.)

You should figure the discount by either the following equivalent
formulas:

=(V10-U10) / V10

=1 - U10 / V10

Note that your formula expresses discount in terms of positive
percentage, not negative.

when the fixed price is not a dollar amount but a
percent example 10% then my answer is also wrong.

Sure! The above formula works only if all numbers are dollar amounts.

I don't know what it means to express fixed __price__ as a
percentage. Percentage of what?

If you can answer that question, you should know how to change the
formula. For example, if U10 is a percentage of Y10, then:

=1 - U10*Y10 / V10.

However, U10 must consistently be either dollars or percentage. The
same simple formula cannot be used for both cases. It could be made
to work if you have some other cell that tells you what "flavor" is in
U10. For example:

=1 - if(Z10 = 1, U10, U10*Y10) / V10

where Z10 is 1 if U10 is dollars, and Z10 is 0 if U10 is percentage.

But that is messy. It is better if U10 is always dollars, perhaps
derived from a percentage of something else, both of which values are
in other cells.


----- original posting -----