Index Match variable value - greater than less than question

[karcpr]

Hi everyone,
I have been doing greater than less than in a HUGE nested AND function, however, I understand that there is a very simple way to do a index + match that will capture greater than (or equal) / less than (or equal). Can you help me to ID if this formula exists? See below for a sample example.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[TABLE="width: 1200"]
<tbody>[TR]
[TD]cost 1[/TD]
[TD]cost 2[/TD]
[TD]cost 3[/TD]
[TD]cost 4[/TD]
[TD]cost 5[/TD]
[TD]cost 6[/TD]
[TD]cost 7[/TD]
[TD][/TD]
[TD]break 1[/TD]
[TD]break 2[/TD]
[TD]break 3[/TD]
[TD]break 4[/TD]
[TD]break 5[/TD]
[TD]break 6[/TD]
[TD]break 7[/TD]
[TD][/TD]
[TD]6 month demand[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.035[/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[TD] 1,000[/TD]
[TD] 2,500[/TD]
[TD] 5,000[/TD]
[TD] 10,000[/TD]
[TD] 25,000[/TD]
[TD] 500,000[/TD]
[TD] 100,000[/TD]
[TD][/TD]
[TD] 3,500[/TD]
[/TR]
[TR]
[TD="align: right"]0.095[/TD]
[TD="align: right"]0.07[/TD]
[TD="align: right"]0.06[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0.04[/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]0.025[/TD]
[TD][/TD]
[TD] 1,000[/TD]
[TD] 2,500[/TD]
[TD] 5,000[/TD]
[TD] 10,000[/TD]
[TD] 25,000[/TD]
[TD] 500,000[/TD]
[TD] 100,000[/TD]
[TD][/TD]
[TD] 5,800[/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisM]

You haven't specified what you are trying to accomplish in your sample data....

 

[karcpr]

Sorry for the misdirection.

My goal is to find where the 6 month demand falls into the break column and as result come up with the cost that is associated with it. Break 1 is associated with cost 1, break 2 = cost 2 and so on.

In the old way of doing everything this is what I am trying to get (these would be nested into each other).

BREAK 1
IF(6 MONTH DEMAND < BREAK 2, COST 1,)

BREAK 2
IF(AND(6 MONTH DEMAND BREAK 2)),COST 2,)

BREAK 3
IF(AND(6 MONTH DEMAND BREAK 3)),COST 3,)

BREAK 4
IF(AND(6 MONTH DEMAND BREAK 4)),COST 4,)

BREAK 5
IF(AND(6 MONTH DEMAND BREAK 5)),COST 5,)

BREAK 6
IF(AND(6 MONTH DEMAND BREAK 6)),COST 6,)

BREAK 7
IF(6 MONTH DEMAND>BREAK 7,COST7,)

 

[ChrisM]

I have to run, so haven't tested this, but this should get you close:

=INDEX(A1:G1,,IFERROR(MATCH(O1,H1:N1,1),1))

where A1:G1 is cost, O1 is demand lookup, and H1:N1 is demand breaks.

 

[karcpr]

That is great. What function does the Iferror play? I know what iferror does, however, how does the iferror differentiate the part?

I have an addition to the question on this process but I will start start a new thread.

 

[ChrisM]

Match is used with the 1 flag here, which means it is in "find the smallest match" mode. However, it will error out if you enter a value smaller than your first demand break. Your first break is at 1,000, so if you try to look up 750 you will get an error. I assumed everything below 1,000 should return the same break as 1000-2499, so IFERROR(xxx,1) will return the value 1 in this case, and when you pass 1 to the INDEX function, it will return the first cost break.

If that logic is not correct, we'll either need to change the formula or have you add a zero demand cost break.

 

[karcpr]

Totally makes sense.

 

[karcpr]

Okay, here is a quick change in the thought process. This is how the pricing file usually looks when it is transmitted over to us.

[TABLE="width: 887"]
<colgroup><col span="6"><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Cost1[/TD]
[TD]Cost2[/TD]
[TD]Cost3[/TD]
[TD]Cost4[/TD]
[TD]Cost5[/TD]
[TD]Cost6[/TD]
[TD]Max Qty1[/TD]
[TD]Max Qty2[/TD]
[TD]Max Qty3[/TD]
[TD]Max Qty4[/TD]
[TD]Max Qty5[/TD]
[TD]Max Qty6[/TD]
[TD]6 MONTHS SUPPLY[/TD]
[/TR]
[TR]
[TD="align: right"]3.59[/TD]
[TD="align: right"]2.49[/TD]
[TD="align: right"]1.82[/TD]
[TD="align: right"]1.72[/TD]
[TD="align: right"]1.68[/TD]
[TD="align: right"]1.63[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]2499[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]9999[/TD]
[TD="align: right"]9999999[/TD]
[TD] 499[/TD]
[/TR]
[TR]
[TD="align: right"]42.99[/TD]
[TD="align: right"]36.51[/TD]
[TD="align: right"]31.21[/TD]
[TD="align: right"]22.97[/TD]
[TD="align: right"]21.79[/TD]
[TD="align: right"]21.2[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]9999999[/TD]
[TD] 69[/TD]
[/TR]
[TR]
[TD="align: right"]0.4553[/TD]
[TD="align: right"]0.4009[/TD]
[TD="align: right"]0.3602[/TD]
[TD="align: right"]0.333[/TD]
[TD="align: right"]0.2922[/TD]
[TD="align: right"]0.2786[/TD]
[TD="align: right"]9999[/TD]
[TD="align: right"]19999[/TD]
[TD="align: right"]49999[/TD]
[TD="align: right"]99999[/TD]
[TD="align: right"]249999[/TD]
[TD="align: right"]999999999[/TD]
[TD] 21,898[/TD]
[/TR]
[TR]
[TD="align: right"]0.074[/TD]
[TD="align: right"]0.0714[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]49999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 106,418[/TD]
[/TR]
[TR]
[TD="align: right"]0.0999[/TD]
[TD="align: right"]0.0864[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 26,584[/TD]
[/TR]
[TR]
[TD="align: right"]4.74[/TD]
[TD="align: right"]3.74[/TD]
[TD="align: right"]3.21[/TD]
[TD="align: right"]2.85[/TD]
[TD="align: right"]2.37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]9999[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD] 1,954[/TD]
[/TR]
[TR]
[TD="align: right"]1.44[/TD]
[TD="align: right"]1.34[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 2,462[/TD]
[/TR]
[TR]
[TD="align: right"]1.81[/TD]
[TD="align: right"]1.69[/TD]
[TD="align: right"]1.57[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 6,000[/TD]
[/TR]
[TR]
[TD="align: right"]2.55[/TD]
[TD="align: right"]2.02[/TD]
[TD="align: right"]1.89[/TD]
[TD="align: right"]1.76[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]499[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]4999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 2,391[/TD]
[/TR]
[TR]
[TD="align: right"]0.0806[/TD]
[TD="align: right"]0.0697[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 22,796[/TD]
[/TR]
[TR]
[TD="align: right"]0.0815[/TD]
[TD="align: right"]0.075[/TD]
[TD="align: right"]0.0707[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]49999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 48,800[/TD]
[/TR]
[TR]
[TD="align: right"]0.0839[/TD]
[TD="align: right"]0.0725[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 137,500[/TD]
[/TR]
[TR]
[TD="align: right"]0.1404[/TD]
[TD="align: right"]0.132[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]249999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 29,805[/TD]
[/TR]
[TR]
[TD="align: right"]0.079[/TD]
[TD="align: right"]0.0728[/TD]
[TD="align: right"]0.0686[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]24999[/TD]
[TD="align: right"]49999[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 34,861[/TD]
[/TR]
[TR]
[TD="align: right"]0.0627[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9999999[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] 1,259,500[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 7"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is the format that everything currently is in. My issue is that I can manipulate the data to the min qtys and take out the 0's but when we are working on monsterous files, it makes it extremely time consuming. Do you know of a way that we can use the same formula and instead of using the pricing prior, you use the pricing max? The 6 month supply would still be what you are trying to get to.

To add complexity to the matter, would it be out of the realm of crazy to add a tolerance to the 6 month supply to state "if the 6 month supply is within x% of the next break, go to the next break. I theoretically could make a new column that that has an if function that states if 20% more is not the same break as the 6 month supply, to do the same formula. Does that make sense?