Search results

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...

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...

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

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...

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...

Alternatively, could I just get Hlook up to return the next largest quantity and its price? Thanks

I am having trouble returning the next highest value using HLOOKUP. For example: My table array (data) = 10, 20, 30, 40, 50 If my lookup value is 34, HLookup returns 30. I would like it to return 40 (round up instead of down). Any help would be greatly appreciated

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...

Hi Hui, Thanks so much for the prompt reply. That worked perfectly. Really appreciate it, Sam

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...

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...

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...