Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-06-2017, 09:50 AM
abc3132 abc3132 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2015
Posts: 9
abc3132 is on a distinguished road
Default Price with different break points

Hi all.
Im 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
Attached Files
File Type: xlsx Prices with different breakpoint.xlsx (8.8 KB, 3 views)
Reply With Quote
  #2  
Old 12-06-2017, 01:14 PM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
A Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 129
NBVC is on a distinguished road
Default

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)))
Reply With Quote
  #3  
Old 12-06-2017, 02:26 PM
abc3132 abc3132 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2015
Posts: 9
abc3132 is on a distinguished road
Default

Thanks NBVC
Im 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
Reply With Quote
  #4  
Old 12-07-2017, 04:11 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 88
ArviLaanemets is on a distinguished road
Default

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
File Type: xlsx Copy of Prices with different breakpoint.xlsx (13.9 KB, 3 views)
Reply With Quote
  #5  
Old 12-07-2017, 04:44 AM
abc3132 abc3132 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Feb 2015
Posts: 9
abc3132 is on a distinguished road
Default

Yep your solutions works very well.
Its good idea.
Thanks for your time and effort
Reply With Quote
  #6  
Old 12-07-2017, 06:52 AM
NBVC's Avatar
NBVC NBVC is offline Windows 10 Office 2013
A Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 129
NBVC is on a distinguished road
Default

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
File Type: xlsx Copy of Prices with different breakpoint.xlsx (9.2 KB, 1 views)
Reply With Quote
Reply

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


All times are GMT -7. The time now is 11:49 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft