• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Uni Assingment help - matching prices for shopping items

S

samlim88

New Member
Hi everyone,


I am doing some excel analysis for a university assignment, and I would be very grateful of any help on a problem I have.


The analysis basically has 2 data components to it:

The 1st part, is a basic transaction list of shopping items bought through the year. Each transaction's shopping item also has the quantity of that item purchased at that time.


The 2nd part, is a pricing sheet for all the different types of shopping items. The pricing sheet has different prices for different quantities at which the item is purchased.


What I am trying to do is to find the relevant price for shopping item, which depends on not only what the item is, but also the quantity. In point form, it should follow the logic below:


1) Identify the item in the shopping list (worksheet 1) from the list of prices (worksheet 2)

2) Find quantity in the prices worksheet that is closest to the quantity in the shopping list (i.e. where the difference between the quantity on transaction list and the quantity on the pricing sheet by lest magnitude)

3) Pull the price for this "closest quantity"


I have uploaded a worksheet showing the structure of that data.

http://www.megaupload.com/?d=DALNF9LG


I have also actually made an attempt at doing this, and come up with the correct output. But, it seems way too complicated for something which is so simple, so I am hoping one of you excel gurus can help me make it more simpler.


Is there some VB code I need to do this, or can it just be a few simple formulaes?


Many Thanks,

Sam
 
Please be aware I have also posted this question to the following forums (apologies, I have been posting away, but not realising the implications of not making people aware of this. It is now obvious to me that cross posting may be waste people's time given the question may have already been answered elsewhere)


http://www.mrexcel.com/forum/showthread.php?t=438807

http://www.ozgrid.com/forum/showthread.php?t=141754

http://chandoo.org/forums/topic/uni-...shopping-items

http://www.excelforum.com/excel-gene...ing-items.html


Thanks for udnerstanding,

Sam
 
Samlim


Try the following in Cell F7 on the "What I'm trying to do" sheet

=+HLOOKUP($D7,OFFSET($J$5:$N$6,MATCH($C7,$I$7:$I$16),0),2,TRUE)


You may need to add a set of values for when the Quantity is less than the Minimum

ie: When C = 88 the Minimum Qty is 100 and so an error occurs

Insert a Column Before J and set the values to Zero and the Minimum Price accordingly


If you do that change the formula to

=+HLOOKUP($D7,OFFSET($J$5:$O$6,MATCH($C7,$I$7:$I$16),0),2,TRUE)
 
Many thanks for the response Hui.


But the HLOOKUP only finds the closest match where the item your matching to is less than the item your matching from.


I.e. If D7 was 35 instead, it would return a price for the quantity of 20. This is incorrect as 35 is closest to the quantity to 40. The price should therefore be $1.00 and not $1.12.


Do you have any suggestions for this Hui?


Many Thanks again,

Sam
 
Sam

I would insert a line between the Qty and Price for each Item

in that line put the Mid Point Qty and then change the formula in F7 to the following

=+HLOOKUP($D7,OFFSET($J$6:$O$8,MATCH($C7,$I$8:$I$20),0),2,TRUE)

So you are now referring to the Mid Point Qty instead of the Original Qty

and the HLookup will work correctly

The Mid Point Qty could be keyed or a formula

so for Item B you will have

0 20 40 60 80 100 Quantities

0 10 30 50 70 90 Mid Point Qty

B $1.12 $1.12 $1.00 $0.90 $0.80 $0.90 Price
 
Hi Hui,


Could you help me figure out doing an alternative form of calculating the price.


So instead of taking the price for the closest quantity, would it be possible for the Hlookup to take prices for the quantities both above and below our list quantity, then draw a straight line (gradient) for the two points, and then using that gradient calculate the price for our list quantity.


For example, in C8 I have Item "A" with a quantity of "13". So the formulae would take the prices for the quantities 10 and 20, which are $1.00 and $0.86. It would then calculare the gradient for the straight line between the two quantities and calculate the price for quantity "13". That is, $0.96.


From my understanding the calculation would be as follows ($1.00 - $0.86)/(10 - 20)*(13 - 10) + #1.00


I have also attached a revised http://www.megaupload.com/?d=Q3OT0SVU


Thanks,

Sam
 
samlim88,


If you post the same question in other forums please provide a link to the post


Please read this http://www.excelguru.ca/node/7
 
Samlim


Try the following in H7 and copy down


=+TREND(OFFSET($K$5,+MATCH($C7,$J$8:$J$20,1)+2,+MATCH($D7,OFFSET($K$5:$P$5,MATCH($C7,$J$8:$J$20,1),0))-1,1,2),OFFSET($K$5,+MATCH($C7,$J$8:$J$20,1),+MATCH($D7,OFFSET($K$5:$P$5,MATCH($C7,$J$8:$J$20,1),0))-1,1,2),$D7)


It uses the Trend Function to do the maths, and lots of Match's and Offsets to get the values into the Trend correct.


It returns the same results you had manually calculated
 
Thanks Hui,


If I wanted to remove the quantities between the Bolded quantities and Price (i.e. quantities in row 7, 11, etc.), what would the formulae look like.


Is there anyway you could help me understand the logic/structure of the formulae. I think this is something I will be using quite often in the future as well.


Thanks,

Sam
 
Samlim

Delete the lines 7, 11 etc and

Copy this to H6 or 7 and copy down


=+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)


I'll post an explanation in the next few days
 
Thanks Hui,


That works great. Understanding this would be great.


Also, when the quantity exceeds the max quantity, a value problem occurs.


Is there anyway to make set it so that the price is equal to the price of maximum quantity.


For example, if we have a quantity of 100 for item A, but the max is quantity is 50 at a price of $0.74, can we then set the price at $0.74.


Thanks,

Sam
 
The quickest way is to insert another column after the Maximum Qty and put in a Large Qty at the same price as the Maximum Qty

Then Select the formula and then Drag the relevant ranges to cover the new Column
 
I was also just playing around with your HLookup and realised it does not provide an exact match for the item name. Is there anyway to make it an exact match?


For example, even if I had item name "asd" it would still show a price even though it did not exist on the price list.


Thanks,

Sam
 
Samlim


I hope the following helps with an explanation of the formula


=+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)


is based on the Trend formula

=Trend(y_range, x_Range, New_X, Const)


Trend returns a new Y_value for a New_X value based on the X_Range, Y_Range data and interpolates using best fit lines

X_Range and Y_Range are both arrays of data and can be entered as 1,2,3 or as a range A1:A10

Const is not required for this exercise


If X_Range and Y_Range are linear we could have used the total Range for Qty and Price

As your data was not linear, we have to work out a shorter range, being the Qty and Price either side of your Qty driver.

ie: the X_Range and Y_Ranges will only be 2 cells long


So applying Trend to your data requires that we can work out the location of these 2 ranges based on the Qty and Item.


There are 3 parts to the Trend equation


which are


Y_Range = OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1), +MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2)


X_Range = OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1, +MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2)


New_X = $D7


How does the Offset work to setup a Range related to your data?


Y_Range is based around the calculation of the Relevent Row and Column for the first value of the Y_Range,

which in this case of your Data is Item=B Qty=38 is the $1.12

Offset will lookup the Offseting Rows and Columns from the top of the data $J$6


The first part of the offset is the Rows which is +MATCH($C7,$J$7:$J$16,1)

This Looks up the No. of Rows based on the Position of $C7 (Item B) in the column ($J$7:$J$16) which in this case is 4


The second part of the offset is the Columns which is +MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0))

This part looks up the horizontal offset along the Qty Row, But seeing as this is variable we have to offset a fixed row to the correct position first

The Midlle part of this : OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)

does the job of offsetting a known Range $K$6:$P$6 and it is moved down the number of rows as discussed above based on the Item No. (Item B) in the column


($J$7:$J$16) which in this case is 4


The outside part of this +MATCH($D7,________, ) then can workout how far along the Qty Range to offset the new range, which in this case is 2


The third part of the offset is the size of the Rows in this case we are after an Range which is 1 cell high


The fourth part of the offset is the size of the Columns in this case we are after an Range which is 2 cells wide


X_Range works the same except for a small -1 after the first match in the X_Range, which then looks up the corresponding cell 1 row above


You may have to read through this very slowly and understand each section before you move on.


Believe me when I say, that it did my head in writing the formula in the first place!
 
Wow, increadible Hui! Thanks so much for the detailed explanation.


I'm taking my time to read it now.


By the way, would you help me answer the below please?


----

I was also just playing around with your HLookup and realised it does not provide an exact match for the item name. Is there anyway to make it an exact match?


For example, even if I had item name "asd" it would still show a price even though it did not exist on the price list.


Thanks,

Sam

----
 
I didn't realise Hui, but the formule [ =+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7) ] doesn't provide an exact match.


So if I have item Z for example, it still returns a result.


Is it possible to make it return a value for only when there is an exact match?
 
Hi Samlim


have you thought about putting some data validation on the input cells rather than try to fix it via formula?

They are already very complex and difficult to follow
 
You must log in or register to reply here.
Back
Top