IF then statements-Tiered PRicing

J

Jackiec21

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
Click to expand...

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

Jackiec21

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

Jackiec21

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!
 
D

Dave F

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
 
T

Toppers

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
 
E

Epinn

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

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
 
K

Kevin James

=AND(B6<100,B6>0)*10+(B6=100)*8+(B6>100)*5

--
Kevin James.
Tua'r Goleuni



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

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
 
K

Kevin James

Er... No.

But there's a full explanation here,

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

HTH,
--
Kevin James.
Tua'r Goleuni


| Hi Kevin
|
| > Tua'r Goleuni
|
| Towards the light
| Did you by any chance attend Cardiff High School?
|
| --
| Regards
|
| Roger Govier
|
|
| | > =AND(B6<100,B6>0)*10+(B6=100)*8+(B6>100)*5
| >
| > --
| > Kevin James.
| > Tua'r Goleuni
| >
| >
| > | >>> 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
| >
| > | > 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(X>100,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!
| >> > > >
| >
| >
|
|
 
R

Roger Govier

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.
 
E

Epinn

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,B6>0)*10+(B6=100)*8+(B6>100)*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

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.
 
R

Roger Govier

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.
Click to expand...

Kevin's formula (adjusted to relate to the same cell A1)
=AND(A1<100,A1>0)*10+(A1=100)*8+(A1>100)*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


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,B6>0)*10+(B6=100)*8+(B6>100)*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

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.
 
E

Epinn

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

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.
Click to expand...

Kevin's formula (adjusted to relate to the same cell A1)
=AND(A1<100,A1>0)*10+(A1=100)*8+(A1>100)*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


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,B6>0)*10+(B6=100)*8+(B6>100)*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

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.
 
Top