If Statements in Excel?

Question

Hello,

How do I create a nested IF statement in Excel for the following:

Lets say we have an order amount and to find out the cost the criteria is:

$250 per order for the first 250 orders per day;
$120.00 per order for the next 100 orders per day:
$75.00 per order for the next 100 per day:
$50.00 per order for any additional orders per day;

The answer for 684 orders should be $98,700

If there is a better way then that would be great.

Thanks,

esco

Answer 1

If C11 is the number of orders that day, then:-



=IF(C11<250,C11*250,

IF(C11<350,((C11-250)

*120)+62500,IF(C11<450

,((C11-350)*75)+74500,

((C11-450)*50)+82000)))



But i don't know where you get 98,700 from, according to my calculations, it should be 93,700.



Sorry for splitting the formula over multiple lines, if i don't, then yahoo removes half the line and just does ... at the end. This is indeed a single formula and should all go in a single cell with no line returns. You did ask for a nested If statement...

Answer 2

OK, your using a spreadsheet to do database functions. But I'll play along. I've been stuck with trying to jerk off the impossible with less than desirable resources.



I set up a sheet just to see if I could do this.

If you are keeping all you orders in a spreadsheet meaning you have multiple days of orders you will need to sort on the date field first.

Then I use this formula to count orders of a similar date.

=IF(C2=C1,B1+1,1)

Column C contains the date of the order and I increment the count for column B where the previous date is the same as the current one. If its different I start the count over.



=IF(B2<10,$A$2,

IF(B2<21,$A$3,

IF(B2<1000,$A$4)))



(the previous 3 lines are meant to be one line)

A2 (has $250) is the cell with the value I wish to apply for the first group of orders, A3 is the cell with the second value and A4 has the final value. Then subtotal based on date for the price per order column.

I used different breaks than you did because I didnt want to build a huge spread sheet just for testing.

I see some things here that could get a lot fancier if you wanted. You could save your price breaks and have different ones for different customers if you do such a thing.

Send me an email and I will send you my spreadsheet example if you wish. (its very small a basic but make the demo a little easier to visualize)



[e-mail]

Answer 3

I got ill edit when im done. =D

Edit: Should be 93,700

A1: input (684)

A2: =IF(A1<=250,250*A1,0)

A3: =IF(AND(A1>250,A1<=350),120*A1-250,0)

A4: =IF(AND(A1>350,A1<=450),75*(A1-350),0)

A5: =IF(A1>450,(A1-450)*50,0)

B2: =IF(A1>250,250*250,0)

B3: =IF(A1>350,120*100,0)

B4: =IF(A1>450,100*75,0)

A6: =A2+A3+A4+A5+B2+B3+B4+B5



Works, =)