Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

Thank you for your quick response. If X=100, then price is $8.00, so how do
I need to enter that one??

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

=IF(X<100,10,IF(X=100,8,IF(X100,5)))

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Thank you for your quick response. If X=100, then price is $8.00, so how do
I need to enter that one??

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

=IF(X<100,10,IF(X=100,8,5))

"Jackiec21" wrote:

Thank you for your quick response. If X=100, then price is $8.00, so how do
I need to enter that one??

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what your
ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

Assume A1 has the number of widgets ordered, then to calculate price in (say)
B1:

=IF(A1<100,10,IF(A1=100,8,5))

HTH


"Dave F" wrote:

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what your
ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

Yeah, that defines the ELSE clause.
--
Brevity is the soul of wit.


"Toppers" wrote:

Assume A1 has the number of widgets ordered, then to calculate price in (say)
B1:

=IF(A1<100,10,IF(A1=100,8,5))

HTH


"Dave F" wrote:

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what your
ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

Assume A1 has the number of widgets ordered ......

I like what you said ...... *ordered* is the keyword.

If the user enters a negative number to A1 to indicate the number of widgets *returned*, say -200, then the pricing will be off.

Yes, the poster did say "order." I just want to highlight the fact that there shouldn't be any negative numbers in A1.

Epinn

"Toppers" wrote in message ...
Assume A1 has the number of widgets ordered, then to calculate price in (say)
B1:

=IF(A1<100,10,IF(A1=100,8,5))

HTH


"Dave F" wrote:

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what your
ELSE clause is, which you haven't done, or, otherwise, suppress errors, as in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then the price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this work
and this is what my boss wants. Thanks!

=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5

--
Kevin James.
Tua'r Goleuni


"Epinn" wrote in message
...
Assume A1 has the number of widgets ordered ......

I like what you said ...... *ordered* is the keyword.

If the user enters a negative number to A1 to indicate the number of widgets
*returned*, say -200, then the pricing will be off.

Yes, the poster did say "order." I just want to highlight the fact that there
shouldn't be any negative numbers in A1.

Epinn

"Toppers" wrote in message
...
Assume A1 has the number of widgets ordered, then to calculate price in (say)
B1:

=IF(A1<100,10,IF(A1=100,8,5))

HTH


"Dave F" wrote:

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what your
ELSE clause is, which you haven't done, or, otherwise, suppress errors, as
in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is 95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then the
price
is $10.00. If the customer orders 105 widgits (or some other number) than
the price is $5.00. I am still not clear on how to get a price to
calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make this
work
and this is what my boss wants. Thanks!

Hi Kevin

Tua'r Goleuni

Towards the light
Did you by any chance attend Cardiff High School?

--
Regards

Roger Govier


"Kevin James" wrote in message
...
=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5

--
Kevin James.
Tua'r Goleuni


"Epinn" wrote in message
...
Assume A1 has the number of widgets ordered ......

I like what you said ...... *ordered* is the keyword.

If the user enters a negative number to A1 to indicate the number of
widgets
*returned*, say -200, then the pricing will be off.

Yes, the poster did say "order." I just want to highlight the fact
that there
shouldn't be any negative numbers in A1.

Epinn

"Toppers" wrote in message
...
Assume A1 has the number of widgets ordered, then to calculate price
in (say)
B1:

=IF(A1<100,10,IF(A1=100,8,5))

HTH


"Dave F" wrote:

Assume A1 has the number of widgets ordered.

Then:

=IF(A1=95,10,IF(A1=105,5))

If you want to avoid the FALSE value, you have to either specify what
your
ELSE clause is, which you haven't done, or, otherwise, suppress
errors, as
in:

=IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is
95,
THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

What I am trying to do is say-if a customer orders 95 widgits, then
the
price
is $10.00. If the customer orders 105 widgits (or some other
number) than
the price is $5.00. I am still not clear on how to get a price to
calculate,
as I received a "False" answer. Thanks for any additional help!

"Dave F" wrote:

=IF(X<100,10,IF(X100,5))

You don't specify what happens if X = 100.

Dave
--
Brevity is the soul of wit.


"Jackiec21" wrote:

Please help!

I am going to have to create an Excel spread using tiered
pricing. I
imagine it will be:

IF QUANTITY PRICE
<100 10.00
100 5.00

Please help-I have no idea how to use an "IF" statement to make
this
work
and this is what my boss wants. Thanks!

Er... No.

But there's a full explanation here,

http://www.btinternet.com/~kevin.james1/Tuar.htm

HTH,
--
Kevin James.
Tua'r Goleuni


"Roger Govier" wrote in message
...
| Hi Kevin
|
| Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| "Kevin James" wrote in message
| ...
| =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
|
| --
| Kevin James.
| Tua'r Goleuni
|
|
| "Epinn" wrote in message
| ...
| Assume A1 has the number of widgets ordered ......
|
| I like what you said ...... *ordered* is the keyword.
|
| If the user enters a negative number to A1 to indicate the number of
| widgets
| *returned*, say -200, then the pricing will be off.
|
| Yes, the poster did say "order." I just want to highlight the fact
| that there
| shouldn't be any negative numbers in A1.
|
| Epinn
|
| "Toppers" wrote in message
| ...
| Assume A1 has the number of widgets ordered, then to calculate price
| in (say)
| B1:
|
| =IF(A1<100,10,IF(A1=100,8,5))
|
| HTH
|
|
| "Dave F" wrote:
|
| Assume A1 has the number of widgets ordered.
|
| Then:
|
| =IF(A1=95,10,IF(A1=105,5))
|
| If you want to avoid the FALSE value, you have to either specify what
| your
| ELSE clause is, which you haven't done, or, otherwise, suppress
| errors, as
| in:
|
| =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1 is
| 95,
| THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| What I am trying to do is say-if a customer orders 95 widgits, then
| the
| price
| is $10.00. If the customer orders 105 widgits (or some other
| number) than
| the price is $5.00. I am still not clear on how to get a price to
| calculate,
| as I received a "False" answer. Thanks for any additional help!
|
| "Dave F" wrote:
|
| =IF(X<100,10,IF(X100,5))
|
| You don't specify what happens if X = 100.
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| Please help!
|
| I am going to have to create an Excel spread using tiered
| pricing. I
| imagine it will be:
|
| IF QUANTITY PRICE
| <100 10.00
| 100 5.00
|
| Please help-I have no idea how to use an "IF" statement to make
| this
| work
| and this is what my boss wants. Thanks!
|
|
|
|
|

Hi Kevin

Thank you for that. Very interesting information.

Being an old boy (well, a very old boy<g) of Cardiff High School, I
"saw the light" as that was also our school motto.
No reason of course why we should have had a monopoly it its use and I'm
delighted to know that Bargoed Grammar used it as well.


--
Regards

Roger Govier


"Kevin James" wrote in message
...
Er... No.

But there's a full explanation here,

http://www.btinternet.com/~kevin.james1/Tuar.htm

HTH,
--
Kevin James.
Tua'r Goleuni


"Roger Govier" wrote in message
...
| Hi Kevin
|
| Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| "Kevin James" wrote in message
| ...
| =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
|
| --
| Kevin James.
| Tua'r Goleuni
|
|
| "Epinn" wrote in message
| ...
| Assume A1 has the number of widgets ordered ......
|
| I like what you said ...... *ordered* is the keyword.
|
| If the user enters a negative number to A1 to indicate the number
of
| widgets
| *returned*, say -200, then the pricing will be off.
|
| Yes, the poster did say "order." I just want to highlight the
fact
| that there
| shouldn't be any negative numbers in A1.
|
| Epinn
|
| "Toppers" wrote in message
| ...
| Assume A1 has the number of widgets ordered, then to calculate
price
| in (say)
| B1:
|
| =IF(A1<100,10,IF(A1=100,8,5))
|
| HTH
|
|
| "Dave F" wrote:
|
| Assume A1 has the number of widgets ordered.
|
| Then:
|
| =IF(A1=95,10,IF(A1=105,5))
|
| If you want to avoid the FALSE value, you have to either specify
what
| your
| ELSE clause is, which you haven't done, or, otherwise, suppress
| errors, as
| in:
|
| =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1
is
| 95,
| THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| What I am trying to do is say-if a customer orders 95 widgits,
then
| the
| price
| is $10.00. If the customer orders 105 widgits (or some other
| number) than
| the price is $5.00. I am still not clear on how to get a price
to
| calculate,
| as I received a "False" answer. Thanks for any additional
help!
|
| "Dave F" wrote:
|
| =IF(X<100,10,IF(X100,5))
|
| You don't specify what happens if X = 100.
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| Please help!
|
| I am going to have to create an Excel spread using tiered
| pricing. I
| imagine it will be:
|
| IF QUANTITY PRICE
| <100 10.00
| 100 5.00
|
| Please help-I have no idea how to use an "IF" statement to
make
| this
| work
| and this is what my boss wants. Thanks!
|
|
|
|
|

Hi Roger,

I was hoping that you found your long lost classmate. I wish I know how to pronounce "Tua'r Goleuni." I have never attended schools in the U.K., but I once had a Welsh teacher.

<< (well, a very old boy<g)

I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg But, let me say this, people who "tolerate" me well are usually over 50. If you are younger than 50, please don't feel offended. It's a compliment.

Now, on to my main reason to post. I want to let you and Kevin know that I like this formula. To borrow Bob's words, "what little gem!"

=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5

Roger, I think I can see "+" as "or" *without* the presence of SUMPRODUCT. Your lecture on Boolean is beginning to see some results. Bob, are you here?

I also learn that if B6 is <0, 0 will be displayed because FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc.

Okay, I am one big step forward.

Thank you for your attention.

Epinn

"Roger Govier" wrote in message ...
Hi Kevin

Thank you for that. Very interesting information.

Being an old boy (well, a very old boy<g) of Cardiff High School, I
"saw the light" as that was also our school motto.
No reason of course why we should have had a monopoly it its use and I'm
delighted to know that Bargoed Grammar used it as well.


--
Regards

Roger Govier


"Kevin James" wrote in message
...
Er... No.

But there's a full explanation here,

http://www.btinternet.com/~kevin.james1/Tuar.htm

HTH,
--
Kevin James.
Tua'r Goleuni


"Roger Govier" wrote in message
...
| Hi Kevin
|
| Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| "Kevin James" wrote in message
| ...
| =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
|
| --
| Kevin James.
| Tua'r Goleuni
|
|
| "Epinn" wrote in message
| ...
| Assume A1 has the number of widgets ordered ......
|
| I like what you said ...... *ordered* is the keyword.
|
| If the user enters a negative number to A1 to indicate the number
of
| widgets
| *returned*, say -200, then the pricing will be off.
|
| Yes, the poster did say "order." I just want to highlight the
fact
| that there
| shouldn't be any negative numbers in A1.
|
| Epinn
|
| "Toppers" wrote in message
| ...
| Assume A1 has the number of widgets ordered, then to calculate
price
| in (say)
| B1:
|
| =IF(A1<100,10,IF(A1=100,8,5))
|
| HTH
|
|
| "Dave F" wrote:
|
| Assume A1 has the number of widgets ordered.
|
| Then:
|
| =IF(A1=95,10,IF(A1=105,5))
|
| If you want to avoid the FALSE value, you have to either specify
what
| your
| ELSE clause is, which you haven't done, or, otherwise, suppress
| errors, as
| in:
|
| =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1
is
| 95,
| THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| What I am trying to do is say-if a customer orders 95 widgits,
then
| the
| price
| is $10.00. If the customer orders 105 widgits (or some other
| number) than
| the price is $5.00. I am still not clear on how to get a price
to
| calculate,
| as I received a "False" answer. Thanks for any additional
help!
|
| "Dave F" wrote:
|
| =IF(X<100,10,IF(X100,5))
|
| You don't specify what happens if X = 100.
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| Please help!
|
| I am going to have to create an Excel spread using tiered
| pricing. I
| imagine it will be:
|
| IF QUANTITY PRICE
| <100 10.00
| 100 5.00
|
| Please help-I have no idea how to use an "IF" statement to
make
| this
| work
| and this is what my boss wants. Thanks!
|
|
|
|
|

Hi Epinn

Your initial comment concerned the formula posted by Toppers
=IF(A1<100,10,IF(A1=100,8,5)) and concerned

If the user enters a negative number to A1 to indicate the number of
widgets *returned*,
say -200, then the pricing will be off.
....... I just want to highlight the fact that there shouldn't be
any negative numbers in A1.

Kevin's formula (adjusted to relate to the same cell A1)
=AND(A1<100,A10)*10+(A1=100)*8+(A1100)*5
addresses the issue of negative numbers being entered, and has the merit
of returning 0, as opposed to 10 if cell A1 is empty.

It could be that the original task of the OP was to deal with creating a
Credit, if Widgets were returned, in which case
=IF(ABS(A1)<100,10,IF(ABS(A1)=100,8,5))*SIGN(A1)
will deal with negative quantities and produce the same value per widget
as per the original order, and will return 0 if cell A1 is empty.


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

I was hoping that you found your long lost classmate. I wish I know how
to pronounce "Tua'r Goleuni." I have never attended schools in the
U.K., but I once had a Welsh teacher.

<< (well, a very old boy<g)

I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg
But, let me say this, people who "tolerate" me well are usually over 50.
If you are younger than 50, please don't feel offended. It's a
compliment.

Now, on to my main reason to post. I want to let you and Kevin know
that I like this formula. To borrow Bob's words, "what little gem!"

=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5

Roger, I think I can see "+" as "or" *without* the presence of
SUMPRODUCT. Your lecture on Boolean is beginning to see some results.
Bob, are you here?

I also learn that if B6 is <0, 0 will be displayed because
FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc.

Okay, I am one big step forward.

Thank you for your attention.

Epinn

"Roger Govier" wrote in message
...
Hi Kevin

Thank you for that. Very interesting information.

Being an old boy (well, a very old boy<g) of Cardiff High School, I
"saw the light" as that was also our school motto.
No reason of course why we should have had a monopoly it its use and I'm
delighted to know that Bargoed Grammar used it as well.


--
Regards

Roger Govier


"Kevin James" wrote in message
...
Er... No.

But there's a full explanation here,

http://www.btinternet.com/~kevin.james1/Tuar.htm

HTH,
--
Kevin James.
Tua'r Goleuni


"Roger Govier" wrote in message
...
| Hi Kevin
|
| Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| "Kevin James" wrote in message
| ...
| =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
|
| --
| Kevin James.
| Tua'r Goleuni
|
|
| "Epinn" wrote in message
| ...
| Assume A1 has the number of widgets ordered ......
|
| I like what you said ...... *ordered* is the keyword.
|
| If the user enters a negative number to A1 to indicate the number
of
| widgets
| *returned*, say -200, then the pricing will be off.
|
| Yes, the poster did say "order." I just want to highlight the
fact
| that there
| shouldn't be any negative numbers in A1.
|
| Epinn
|
| "Toppers" wrote in message
| ...
| Assume A1 has the number of widgets ordered, then to calculate
price
| in (say)
| B1:
|
| =IF(A1<100,10,IF(A1=100,8,5))
|
| HTH
|
|
| "Dave F" wrote:
|
| Assume A1 has the number of widgets ordered.
|
| Then:
|
| =IF(A1=95,10,IF(A1=105,5))
|
| If you want to avoid the FALSE value, you have to either specify
what
| your
| ELSE clause is, which you haven't done, or, otherwise, suppress
| errors, as
| in:
|
| =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1
is
| 95,
| THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| What I am trying to do is say-if a customer orders 95 widgits,
then
| the
| price
| is $10.00. If the customer orders 105 widgits (or some other
| number) than
| the price is $5.00. I am still not clear on how to get a price
to
| calculate,
| as I received a "False" answer. Thanks for any additional
help!
|
| "Dave F" wrote:
|
| =IF(X<100,10,IF(X100,5))
|
| You don't specify what happens if X = 100.
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| Please help!
|
| I am going to have to create an Excel spread using tiered
| pricing. I
| imagine it will be:
|
| IF QUANTITY PRICE
| <100 10.00
| 100 5.00
|
| Please help-I have no idea how to use an "IF" statement to
make
| this
| work
| and this is what my boss wants. Thanks!
|
|
|
|
|

Roger,

"That's admirable!" I am totally satisfied now as the formula is perfect and practical meaning it can take care of returned products.

SIGN( ) is quite handy. I was so concerned with negative numbers and I didn't notice an empty cell was a problem too. Hope the OP is still around.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

Your initial comment concerned the formula posted by Toppers
=IF(A1<100,10,IF(A1=100,8,5)) and concerned

If the user enters a negative number to A1 to indicate the number of
widgets *returned*,
say -200, then the pricing will be off.
....... I just want to highlight the fact that there shouldn't be
any negative numbers in A1.

Kevin's formula (adjusted to relate to the same cell A1)
=AND(A1<100,A10)*10+(A1=100)*8+(A1100)*5
addresses the issue of negative numbers being entered, and has the merit
of returning 0, as opposed to 10 if cell A1 is empty.

It could be that the original task of the OP was to deal with creating a
Credit, if Widgets were returned, in which case
=IF(ABS(A1)<100,10,IF(ABS(A1)=100,8,5))*SIGN(A1)
will deal with negative quantities and produce the same value per widget
as per the original order, and will return 0 if cell A1 is empty.


--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

I was hoping that you found your long lost classmate. I wish I know how
to pronounce "Tua'r Goleuni." I have never attended schools in the
U.K., but I once had a Welsh teacher.

<< (well, a very old boy<g)

I won't ask how old you are when I didn't tell you my g _ _ _ _ _. <bg
But, let me say this, people who "tolerate" me well are usually over 50.
If you are younger than 50, please don't feel offended. It's a
compliment.

Now, on to my main reason to post. I want to let you and Kevin know
that I like this formula. To borrow Bob's words, "what little gem!"

=AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5

Roger, I think I can see "+" as "or" *without* the presence of
SUMPRODUCT. Your lecture on Boolean is beginning to see some results.
Bob, are you here?

I also learn that if B6 is <0, 0 will be displayed because
FALSE+FALSE+FALSE = 0+0+0=0. We didn't have to say "if <0,=0" etc.

Okay, I am one big step forward.

Thank you for your attention.

Epinn

"Roger Govier" wrote in message
...
Hi Kevin

Thank you for that. Very interesting information.

Being an old boy (well, a very old boy<g) of Cardiff High School, I
"saw the light" as that was also our school motto.
No reason of course why we should have had a monopoly it its use and I'm
delighted to know that Bargoed Grammar used it as well.


--
Regards

Roger Govier


"Kevin James" wrote in message
...
Er... No.

But there's a full explanation here,

http://www.btinternet.com/~kevin.james1/Tuar.htm

HTH,
--
Kevin James.
Tua'r Goleuni


"Roger Govier" wrote in message
...
| Hi Kevin
|
| Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| "Kevin James" wrote in message
| ...
| =AND(B6<100,B60)*10+(B6=100)*8+(B6100)*5
|
| --
| Kevin James.
| Tua'r Goleuni
|
|
| "Epinn" wrote in message
| ...
| Assume A1 has the number of widgets ordered ......
|
| I like what you said ...... *ordered* is the keyword.
|
| If the user enters a negative number to A1 to indicate the number
of
| widgets
| *returned*, say -200, then the pricing will be off.
|
| Yes, the poster did say "order." I just want to highlight the
fact
| that there
| shouldn't be any negative numbers in A1.
|
| Epinn
|
| "Toppers" wrote in message
| ...
| Assume A1 has the number of widgets ordered, then to calculate
price
| in (say)
| B1:
|
| =IF(A1<100,10,IF(A1=100,8,5))
|
| HTH
|
|
| "Dave F" wrote:
|
| Assume A1 has the number of widgets ordered.
|
| Then:
|
| =IF(A1=95,10,IF(A1=105,5))
|
| If you want to avoid the FALSE value, you have to either specify
what
| your
| ELSE clause is, which you haven't done, or, otherwise, suppress
| errors, as
| in:
|
| =IF(A1=95,10,IF(A1=105,5,"")) In plain English this says: "IF A1
is
| 95,
| THEN 10, ELSE IF A1 is 105, THEN 5, ELSE return an empty string."
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| What I am trying to do is say-if a customer orders 95 widgits,
then
| the
| price
| is $10.00. If the customer orders 105 widgits (or some other
| number) than
| the price is $5.00. I am still not clear on how to get a price
to
| calculate,
| as I received a "False" answer. Thanks for any additional
help!
|
| "Dave F" wrote:
|
| =IF(X<100,10,IF(X100,5))
|
| You don't specify what happens if X = 100.
|
| Dave
| --
| Brevity is the soul of wit.
|
|
| "Jackiec21" wrote:
|
| Please help!
|
| I am going to have to create an Excel spread using tiered
| pricing. I
| imagine it will be:
|
| IF QUANTITY PRICE
| <100 10.00
| 100 5.00
|
| Please help-I have no idea how to use an "IF" statement to
make
| this
| work
| and this is what my boss wants. Thanks!
|
|
|
|
|