#1
|
|||
|
|||
Price with different break points
Hi all.
I´m trying to figure out how to find the price of something in a list that depends of the price break reached...and the break point change with each item. Reference Description Breakpoint €/kg Breakpoint €/kg A Apples 1 2 3 1.5 B Bananas 1 3 6 2 So here if I use VLookup to a fix break point is fine but I do not know how to use when the breakpoint changes Please have a look at the file attached Cheers. Adrian |
#2
|
||||
|
||||
Try:
=IF(A3="","",LOOKUP(2,1/(($H$2:$L$2="Breakpoint")*(INDEX($H$3:$L$5,MATCH($ B3,$F$3:$F$5,0),0)<=$A3)),INDEX($I$3:$M$5,MATCH($B 3,$F$3:$F$5,0),0))) |
#3
|
|||
|
|||
Thanks NBVC
I´m afraid that your solution did not work. However this one yes: =IF(AND(A4>1;A4<VLOOKUP(B4;F3:M5;7;FALSE));VLOOKUP (B4;F3:M5;6;FALSE);0) Cheers |
#4
|
|||
|
|||
An working example is in attached file. I changed the starting break-points from 1 to 0, and in formulas added a calculated MAX break-point value as 999999 (it may be any value Excel can interprete as number, and which is bigger than any real break-point you may have).
I left the reference untouched, but really you can use fruit names instead directly. |
#5
|
|||
|
|||
Yep your solutions works very well.
It´s good idea. Thanks for your time and effort |
#6
|
||||
|
||||
Here is my solution applied to your original data... test it.
Maybe you didn't change the commas in my formula to semi colons to accomodate to your Excel version? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trouble using bullet points / sub bullet points within Word 2013 | Mr Davo | Word | 2 | 04-18-2016 04:31 PM |
Excel: Item and Price | wchristner | Excel | 2 | 07-21-2014 06:23 AM |
Edit Points in Curve: MS Office changes points properties itself, without my action. | Sergey SPb | PowerPoint | 0 | 08-27-2012 05:33 AM |
Yearly price escalator? | markg2 | Excel | 1 | 04-15-2012 01:47 AM |
paragaph hard break, soft break and ...strange break | czomberzdaniela | Word | 2 | 12-03-2010 06:58 PM |