1

My current spreadsheet looks like this:

enter image description here

Unit Cost: one-off cost of an individual item.

1-6: cost multipliers based on volumes required.

Volume: Number of items.

Basic Cost: =( Unit Cost * Volume )

Reduced Cost: =( Unit Cost * Volume * Cost Multiplier)

The formula in my current reduced cost cell looks like this:

=IF(K10=0,0,IF(K10 >= 6,D10*K10*J10,D10*K10*INDEX(E10:J10,1,K10)))

K10: Volume Column

D10: Unit Cost Column

E10:J10: 1-6 Columns

So, if the volume is not 0, or not greater than 6, get the cost multiplier by using the value of volume as a column index.

For example, a volume of 3 will return the third column (0.85).

This worked great initially, and is fairly simple and elegant, but there are new scenarios where it wont work.

In a new scenario, the cost multipliers should only be applied when a volume is greater than a number.

enter image description here

I.E. A volume of 2 would return the first column '1', but a volume of 2.0001 would return the second column '0.9'.

I'm really not sure if there's anyway I could adapt my exist formula to facilitate this, the only other approach I could take right now is a huge nested if statement.

A few points:

  • Volumes can be non-integers
  • There will only ever be 6 cost multiplier categories / thresholds.
W.H
  • 187
  • 2
  • 4
  • 15

3 Answers3

2

All you need here is a MATCH with the third argument set to 1 as long as you change E9:I9 back to numbers (without ">").

=IF(K10=0,0,IF(K10>=6,D10*K10*J10,D10*K10*INDEX(E10:J10,MATCH(K10,E9:J9,1))))
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Perhaps I've used that wrong, but it doesn't seem to work: [Image](http://puu.sh/qqy4T/432b739388.png) =IF(K10=0,0,IF(K10 >=6,D10*K10*J10,D10*K10*INDEX(E10:J10,1,MATCH(K10,E9:J9,1)))) – W.H Aug 05 '16 at 08:46
  • Ah OK, I didn't realize you meant strictly greater than. Yeah, subtracting a tiny amount from the `K10` inside the match (like in the other answer) will make this work. – Alexis Olson Aug 05 '16 at 14:53
1

I know this isn't exactly "Use INDEX() for Greater Than Values".

But, I think this code should work in your case.

=IF(K10<=1,L10,IF(K10 > 6,D10*K10*J10,D10*K10*INDEX(E10:J10,K10-0.000001)))
Eternity Neet
  • 179
  • 4
  • 17
  • Doesn't seem to work, unfortunately, if I add '2' as a volume (K10), the multiplier '0.85' from the second column is used. This value should only be used if the volume is _greater than_ two. – W.H Aug 05 '16 at 11:19
  • Sorry I don't really focus on the `greater than` part, I've edited my answer, so if you put `K10`=`2` the multiplier value will be `0.9` – Eternity Neet Aug 05 '16 at 14:05
  • That actually works pretty well! I don't think it should mess with pricing. Thanks! – W.H Aug 05 '16 at 14:26
0

It might make your head spin a bit, but here is a way to do it without IF functions. It is an array function, so after typing this into cell J2, you have to press CTRL SHIFT ENTER.

=$I2*IFERROR(1/MAX(($H2>$B1:$G1)/$B2:$G2),1)

enter image description here

Ken
  • 1,156
  • 1
  • 6
  • 8