IF Function in incremental charges

[Guest]

I have been working with this formula for two days and can't get it to work
right. Can anyone help?

The criteria is--If column A1 is less than 400 sheets, but not equal to 0,
then charge 2.00. If its more than 400 sheets, then charge is .50 per 100.
The difficulty has been charging .50 per 100 over 400 sheets. It needs to
charge in .50 increments.

Thanks,
Barb

 

[Guest]

Hi

Try this:
=IF(AND(A1>0,A1<400),2,2+INT((A1-400)/50))
This is presuming that when they buy over 400, they still pay the 2.00 for
the first 400.

 

[Guest]

From my answer to your same question 2 days ago

Try

=(2+((A1>400)*0.5*(CEILING(A1/100,1)-4)))*(A1>0)

Which assumes that the charge for 400 is $2, but for 401 it's $2.5

Duke

 

[Guest]

try
=if(A1=0,0,if(A1<400,2.00,ceiling((A1-400)/100,1)*.50))

I assume you want that at 400 it willbe $2.00

 

[Guest]

Thanks, Duke. I tried your solution from the other day and it kept coming up
error in formula for some reason. However, what you posted today this seems
to be working.

Thanks, Barb

 

[Guest]

A little tighter version:

=(MAX(2,0.5*CEILING(A1/100,1)))*(A1>0)

 

[Guest]

Thanks for the feedback Barb

The formula I posted today is cut and pasted from the earlier response, so...

You may want to use the shorter version I posted a little earlier, too

Duke

 

[Guest]

Opps I meant
=if(A1<=0,0,Max(2.00,ceiling((A1-400)/100,1)*.50))

 

[Guest]

Now they've thrown one more variable into the mix. It might change the whole
formula.

These are paper cut charges. The charges are: .50 per cut for 100 sheets
with a 2.00 minimum.

In other words, if one sheet has 3 cuts, its .50 per cut per 100 sheets with
a minimum charge of 2.00. I have column A1 for cuts and column B1 for # of
sheets. Does this make it any more confusing for you because its killing me.

Barb

 

[Guest]

I think only a slight modification is needed

=MAX(2,0.5*A1*CEILING(B1/100,1))*(B1>0)

This will charge $2 for 3 cuts on 100 sheets (instead of $0.50 * 3 cuts * 1
set of 100, or $1.50), and will charge $3.00 for 3 cuts on 101 sheets.

 

[Guest]

You are the man, Duke! It worked perfectly!
Barb

I think only a slight modification is needed

=MAX(2,0.5*A1*CEILING(B1/100,1))*(B1>0)

This will charge $2 for 3 cuts on 100 sheets (instead of $0.50 * 3 cuts * 1
set of 100, or $1.50), and will charge $3.00 for 3 cuts on 101 sheets.