[Solved] IF function & greater than but less than

[idrive]

Here is my spreadsheet setup:
B2 = Total Production [input cell]
B3 = =IF(B2<2000; B2; 2000) [First 2000 Production]
B4 = (2001-2500 Production)
B5 = =IF(B2>2500; B2-2500; 0) [Above 2500 Production]

C3 = $3.00
C4 = $3.50
C5 = $4.00

D3 = =B3*C3
D4 = =B4*C4
D5 = =B5*C5

D6 = =SUM(D3:D5)
-----------------------------------
I need B4 to function as "if B2 is >= 2001 but <= to 2500 then (B2-2000), if b2 is greater than 2500 then (2500-2001), otherwise 0.

The first 2000 Production are on a rate of $3.00, 2001-2500 are on a rate of $3.50, and greater than 2500 are on a rate of $4.00.

Thank you for the help.

[Robert Tucker]

Code: Select all

=IF(AND(B2>=2001;B2<=2500);B2-2000;IF(B2>2500;499;0))

[gerard24]

Hi,

in B4 :

Code: Select all

=IF(B2>2500;500;IF(AND(B2>2000;B2<=2500);B2-2000;0))

You can also make this calculation whithout all these formulae.
Only B2 cell (for input) and in D6 :

Code: Select all

=SUMPRODUCT(B2>{0;2000;2500};B2-{0;2000;2500};{3;0.5;0.5})

or with a table (see attachment).

idrive.ods

(12.4 KiB) Downloaded 920 times

[acknak]

Yow--It looks like your price goes up for larger orders. Did I miss something?

What's to prevent me from submitting 2 separate orders for 2000 units each, just to get the cheaper price?

[idrive]

Haha it's actually a pay scale for shop production...so yes the price does go up for the employee. Thanks for your help Gerard...works great!

[MrProgrammer]

=SUMPRODUCT(B2>{0;2000;2500};B2-{0;2000;2500};{3;0.5;0.5})

I like that method, gerard24, even better than the SUMPRODUCT formula that I posted in Multiple IF calculations for Ebay E-bay FVF fee. It only needs two constants, but my formula used three. I had to study your formula for a while to figure out why it worked, but now I understand it.