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

Hi

Try this:
=IF(AND(A10,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.

--
Andy.


"Barb1" wrote in message
...
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

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

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

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

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

"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

A little tighter version:

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




"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

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

"Barb1" wrote:

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

"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

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

"bj" wrote:

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

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

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

These are paper cut charges. The charges a .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

"Duke Carey" wrote:

A little tighter version:

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




"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

I think only a slight modification is needed

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

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.


"Barb1" wrote:

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

These are paper cut charges. The charges a .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

"Duke Carey" wrote:

A little tighter version:

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




"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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

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

"Duke Carey" wrote:

I think only a slight modification is needed

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

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.


"Barb1" wrote:

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

These are paper cut charges. The charges a .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

"Duke Carey" wrote:

A little tighter version:

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




"Duke Carey" wrote:

From my answer to your same question 2 days ago

Try

=(2+((A1400)*0.5*(CEILING(A1/100,1)-4)))*(A10)

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

Duke



"Barb1" wrote:

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