looking things up in a range part 2

[Lawrence Dodge]

Hi again --
The second question about looking things up in a range is more complicated.
On a different problem than part 1
I have the ranges of:

100,000 - 120,000 lbs. --> 0.25
120,001 - 140,000 lbs. --> 0.30
140,001 - 160,000 lbs. --> 0.35
160,001 - --> 0.40

The way this works is this any thing below 100,000 gets no premium.
If a producer makes 110,000 then 10,000 lbs. gets a premium of $0.25/lb.
If a producer makes 130,000 lbs. then he gets a premium of $0.25 for 20,000 lbs. and then $0.30/lb. for the remaining 10,000.
If a producer makes 155,000 lbs. then he gets a premium of $0.25/lb. for 20,000 lbs., a premium of $0.30 for 20,000 lbs. and a premium of $0.35 for the remaining 15,000 lbs.

Again I have the range on a separate spreadsheet (within the workbook) set up that the low value of a range is in the first column, the high value of a range is in the second column and the premium factor is in the third column, but if there is a better way I can reconstruct this.

Thanks (again)
LCD

 

[SirJB7]

Hi, Lawrence Dodge!

Give a look at the uploaded file. It uses a helper column C (assuming your data is in columns A:B).
A2: value from
B2: premium
C2: =MIN(MAX(0;REDONDEAR(A3-A2;0));D$2-SUMA(C$1:C1)) -----> in english: =MIN(MAX(0,ROUND(A3-A2,0)),D$2-SUM(C$1:C1))
D2: input value
E2: output premium: =SUMAPRODUCTO(C2:C6*B2:B6) -----> in english: =SUMAPRODUCT(C2:C6*B2:B6)

Do note that range values of column A are displayed as integers (100000) but in fact they have a decimal place (100000.1) to handle edge conditions.

Just advise if any issue.

Regards!

 

[Lawrence Dodge]

Hi, Lawrence Dodge!

Give a look at the uploaded file. It uses a helper column C (assuming your data is in columns A:B).
A2: value from
B2: premium
C2: =MIN(MAX(0;REDONDEAR(A3-A2;0));D$2-SUMA(C$1:C1)) -----> in english: =MIN(MAX(0,ROUND(A3-A2,0)),D$2-SUM(C$1:C1))
D2: input value
E2: output premium: =SUMAPRODUCTO(C2:C6*B2:B6) -----> in english: =SUMAPRODUCT(C2:C6*B2:B6)

Do note that range values of column A are displayed as integers (100000) but in fact they have a decimal place (100000.1) to handle edge conditions.

Just advise if any issue.

Regards!

Thanks a lot, it works GREAT once I figured that I had to put a large number in cell A7 to accommodate for A7 - A6, without which everything over 160K went to 18,000
The uploaded sheet has different values and what they should be (rounding ones column aside) before I put 500000 in cell A&.

 

[SirJB7]

Hi, Lawrence Dodge!

In fact column A contains the lowest values for each step, so no need to put a large number in cell A7 (if you're referring to the last entry), I think; it might have been tricky to put a zero in cell A2 (i.e., the first entry), thing that I did and didn't explicitly told you.

But however it was, glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Stop! I suspect that's something that doesn't work as expected. Give a few minutes and I'll be back.

PS 2: Ok, now I got it. You're right the huge number is needed for filling the last step bucket properly, I missed setting it as per the value of 150K of the sample file. And when I read your post something looked strange but didn't get it on the fly. I'll update previous uploaded file.