Microsoft Office Forums Price with different break points
 Register FAQ Search Today's Posts Mark Forums Read

#1
12-06-2017, 09:50 AM
 abc3132 Windows 7 64bit Office 2007 Novice Join Date: Feb 2015 Posts: 14
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.
Attached Files
 Prices with different breakpoint.xlsx (8.8 KB, 4 views)
#2
12-06-2017, 01:14 PM
 NBVC Windows 10 Office 2013 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 192

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
12-06-2017, 02:26 PM
 abc3132 Windows 7 64bit Office 2007 Novice Join Date: Feb 2015 Posts: 14

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
12-07-2017, 04:11 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 302

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.
Attached Files
 Copy of Prices with different breakpoint.xlsx (13.9 KB, 3 views)
#5
12-07-2017, 04:44 AM
 abc3132 Windows 7 64bit Office 2007 Novice Join Date: Feb 2015 Posts: 14

Yep your solutions works very well.
It´s good idea.
Thanks for your time and effort
#6
12-07-2017, 06:52 AM
 NBVC Windows 10 Office 2013 The Formula Guy Join Date: Mar 2012 Location: Mississauga, CANADA Posts: 192

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?
Attached Files
 Copy of Prices with different breakpoint.xlsx (9.2 KB, 2 views)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Mr Davo Word 2 04-18-2016 04:31 PM wchristner Excel 2 07-21-2014 06:23 AM Sergey SPb PowerPoint 0 08-27-2012 05:33 AM markg2 Excel 1 04-15-2012 01:47 AM czomberzdaniela Word 2 12-03-2010 06:58 PM

All times are GMT -7. The time now is 01:04 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top