Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF Function in incremental charges
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |