I am relatively new at doing advanced formulas any help would be greatly
appreciated. Here is my situation I am building a spread sheet that will
calculate commissions for employees.
Column c = national price
Column d = lowest price they can sell the item for
Column u = the price that they sold the item at
Column x is their commission per unit sold
Here is the logic:
If they sell the item for national price (c) they earn 50cents
If they sell the item for lowest price they can sell the item for (d) they
earn 10 cents
If they sell the item for something in between D and C they get 10% of the
difference from where they sold it and the lowest price they can sell it for
Also the lowest commission they can earn is 10 cents on any item sold
Example:
c9=52
d9=49
So if u9=52 then their commission is .50
If u9=49 then their commission is .10
If u9=51 then their commission is 51-49=2.00*.10= .20
The formula I wrote is:
IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1)
The problem I am having is if there is no value in U9 then I get a #value!
Error in x9
I want x9 to be blank if there is no value in u9
Thank you again for any help

Rob, you were almost there. Try:

=IF(U90,IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1),"")

Excel was telling you that you can't divide a number by zero, so the above
tests for a value greater than zero at the outset. If the test returns True,
the formula is used for the result, otherwise, an empty string is entered as
the result.

HTH
Bob



"robc" wrote in message
...
I am relatively new at doing advanced formulas any help would be greatly
appreciated. Here is my situation I am building a spread sheet that will
calculate commissions for employees.
Column c = national price
Column d = lowest price they can sell the item for
Column u = the price that they sold the item at
Column x is their commission per unit sold
Here is the logic:
If they sell the item for national price (c) they earn 50cents
If they sell the item for lowest price they can sell the item for (d) they
earn 10 cents
If they sell the item for something in between D and C they get 10% of the
difference from where they sold it and the lowest price they can sell it
for
Also the lowest commission they can earn is 10 cents on any item sold
Example:
c9=52
d9=49
So if u9=52 then their commission is .50
If u9=49 then their commission is .10
If u9=51 then their commission is 51-49=2.00*.10= .20
The formula I wrote is:
IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1)
The problem I am having is if there is no value in U9 then I get a #value!
Error in x9
I want x9 to be blank if there is no value in u9
Thank you again for any help

Bob thanks for the help but I am still having a problem if I eneter a value
in U9 then delete that value I get the #value! error again is there a way to
stop this from happening?
thanks
Rob

"Bob Alhat" wrote:

Rob, you were almost there. Try:

=IF(U90,IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1),"")

Excel was telling you that you can't divide a number by zero, so the above
tests for a value greater than zero at the outset. If the test returns True,
the formula is used for the result, otherwise, an empty string is entered as
the result.

HTH
Bob



"robc" wrote in message
...
I am relatively new at doing advanced formulas any help would be greatly
appreciated. Here is my situation I am building a spread sheet that will
calculate commissions for employees.
Column c = national price
Column d = lowest price they can sell the item for
Column u = the price that they sold the item at
Column x is their commission per unit sold
Here is the logic:
If they sell the item for national price (c) they earn 50cents
If they sell the item for lowest price they can sell the item for (d) they
earn 10 cents
If they sell the item for something in between D and C they get 10% of the
difference from where they sold it and the lowest price they can sell it
for
Also the lowest commission they can earn is 10 cents on any item sold
Example:
c9=52
d9=49
So if u9=52 then their commission is .50
If u9=49 then their commission is .10
If u9=51 then their commission is 51-49=2.00*.10= .20
The formula I wrote is:
IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1)
The problem I am having is if there is no value in U9 then I get a #value!
Error in x9
I want x9 to be blank if there is no value in u9
Thank you again for any help

The #VALUE error means that one of the cells referenced in the formula is
text, but a number is expected. Did you clear the cell by using the
spacebar?

HTH
Bob



"robc" . wrote in message
...
Bob thanks for the help but I am still having a problem if I eneter a
value
in U9 then delete that value I get the #value! error again is there a way
to
stop this from happening?
thanks
Rob

"Bob Alhat" wrote:

Rob, you were almost there. Try:

=IF(U90,IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1),"")

Excel was telling you that you can't divide a number by zero, so the
above
tests for a value greater than zero at the outset. If the test returns
True,
the formula is used for the result, otherwise, an empty string is entered
as
the result.

HTH
Bob



"robc" wrote in message
...
I am relatively new at doing advanced formulas any help would be greatly
appreciated. Here is my situation I am building a spread sheet that
will
calculate commissions for employees.
Column c = national price
Column d = lowest price they can sell the item for
Column u = the price that they sold the item at
Column x is their commission per unit sold
Here is the logic:
If they sell the item for national price (c) they earn 50cents
If they sell the item for lowest price they can sell the item for (d)
they
earn 10 cents
If they sell the item for something in between D and C they get 10% of
the
difference from where they sold it and the lowest price they can sell
it
for
Also the lowest commission they can earn is 10 cents on any item sold
Example:
c9=52
d9=49
So if u9=52 then their commission is .50
If u9=49 then their commission is .10
If u9=51 then their commission is 51-49=2.00*.10= .20
The formula I wrote is:
IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1)
The problem I am having is if there is no value in U9 then I get a
#value!
Error in x9
I want x9 to be blank if there is no value in u9
Thank you again for any help

yes I used the space bar to erace them. That was the problem I just
backspaced it and they cleared out the error thanks again
Rob

"Bob Alhat" wrote:

The #VALUE error means that one of the cells referenced in the formula is
text, but a number is expected. Did you clear the cell by using the
spacebar?

HTH
Bob



"robc" . wrote in message
...
Bob thanks for the help but I am still having a problem if I eneter a
value
in U9 then delete that value I get the #value! error again is there a way
to
stop this from happening?
thanks
Rob

"Bob Alhat" wrote:

Rob, you were almost there. Try:

=IF(U90,IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1),"")

Excel was telling you that you can't divide a number by zero, so the
above
tests for a value greater than zero at the outset. If the test returns
True,
the formula is used for the result, otherwise, an empty string is entered
as
the result.

HTH
Bob



"robc" wrote in message
...
I am relatively new at doing advanced formulas any help would be greatly
appreciated. Here is my situation I am building a spread sheet that
will
calculate commissions for employees.
Column c = national price
Column d = lowest price they can sell the item for
Column u = the price that they sold the item at
Column x is their commission per unit sold
Here is the logic:
If they sell the item for national price (c) they earn 50cents
If they sell the item for lowest price they can sell the item for (d)
they
earn 10 cents
If they sell the item for something in between D and C they get 10% of
the
difference from where they sold it and the lowest price they can sell
it
for
Also the lowest commission they can earn is 10 cents on any item sold
Example:
c9=52
d9=49
So if u9=52 then their commission is .50
If u9=49 then their commission is .10
If u9=51 then their commission is 51-49=2.00*.10= .20
The formula I wrote is:
IF(U9=C9,0.5,(U9-D9)*0.1)+IF(D9=U9,0.1)
The problem I am having is if there is no value in U9 then I get a
#value!
Error in x9
I want x9 to be blank if there is no value in u9
Thank you again for any help