LinkBack URL
About LinkBacks
Folks,
How do calc this in excel ?
FROM 1 TO 300 I *36
FROM 301 TO 400 I * 43
FROM 401-500 I * 49
=IF(A1<=300,A1*0.36) but how do I continue ?
Folks,
How do calc this in excel ?
FROM 1 TO 300 I *36
FROM 301 TO 400 I * 43
FROM 401-500 I * 49
=IF(A1<=300,A1*0.36) but how do I continue ?
Last edited by davi65; 10-28-2016 at 02:28 PM.
Your question is ambiguous.
if A1= 350, Are the first 300 "sold" at 36 and the rest at 43, or are all 350 "sold" at 43....
more information, please....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Does it have to be an IF() function? How many options are there (or can it expand to)?
My personal preference for something like this, even at 3 options is to use a lookup table and a lookup function. Since most people start with the VLOOKUP() function, I will recommend this as a starting point: https://support.office.com/en-us/art...8-93a18ad188a1
After creating your lookup table, your function would look something like =A1*VLOOKUP(A1,lookup_table,2,TRUE)
Originally Posted by shg
Like this.
1 and all in between 300 $108.00 -->*0.36
301 and all in between 400 $172.00 --> *0.43
401 and all in between 500 $225.00 --> *.45
501 all in and above 600 $294.00 --> 0.49
Like this.
1 and all in between 300 $108.00 -->*0.36
301 and all in between 400 $172.00 --> *0.43
401 and all in between 500 $225.00 --> *.45
501 all in and above 600 $294.00 --> 0.49
it's been a long day here and I'm tired; but your words and your number don't quitte match...
If the no of cases is in A1, is this it:
=SUMPRODUCT(--(A1>{0,300,400,500}),--(A1-{0,300,400,500}),{0.36,0.07,0.02,0.04})
If that earlier one of mine is wrong, then this one will be right:
=A1*LOOKUP(A1,{0,301,401,501},{0.36,0.43,0.45,0.49})
thank you Glenn...
Last edited by davi65; 10-29-2016 at 09:03 AM.
this one is working...
THANK YOU GLENN!
Which one, by the way???
Either way. Glad to have helped...
Last edited by Glenn Kennedy; 10-28-2016 at 03:27 PM.
Here is the sheet.
I want to change the formula to read how much I pay per case on CELL AM,AN ETC.
Attachment 486892
Glenn this one work ->=X6*LOOKUP(X6,{0,301,401,501},{0.36,0.43,0.45,0.49})
Unfortunately, your attachment didn't attach!!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Files attached
Sorry. I only found your reply today. I don't understand. What are you expecting to see in cells in column AM, AN, etc????
Glenn
How do I make a formula do read cells AN-AO-AP-AQ... right now i am using:
=P68*LOOKUP(P68,{0,301,401,501},{0.36,0.43,0.45,0.49})
But if I decide to change the payment from 1-300 to 0.22. I will have to change every single formula I want to have a formula that read the value on AN-AO-AP-AQ
Cases Cases Cases Cases
1-300 301-400 401-500 500-600
0.36 0.43 0.45 0.49
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks