Quantcast

excel wizardry required

ALEXIS_DH

ALEXIS_DH

Tirelessly Awesome
Jan 30, 2003
6,151
798
Lima, Peru, Peru
again, I come to the RM Oracle in search of poo-flinging wisdom.

My goal, is to calculate demand elasticity based on past prices and trends. Am starting with a small sample of items to test a few ideas.

In order to do so, I have the following sheet 1

where:

Column A is SKU ID.
Column B is SKU description
Column J is PRICE
and columns N and O are the start and end dates of validity of PRICE J for a given SKU

in sheet 2, I have



Column A is SKU ID
Column E,F,G... are the first day of the month starting in june 2013.

I need a formula, for sheet 2, to calculate the price of SKU "X", based on what was the price for SKU "X" in the day E2/F2/G2, according to the boundaries set on "Sheet 1".

any ideas???
 
Last edited:
ButtersNZ

ButtersNZ

Monkey
Jun 6, 2013
176
10
So you want the equation to select the price based on the range of dates? As there is no overlap between your dates, perhaps it would help to convert the dates to numbers, create a range, then use the nested "IF function" to specify the price IF the date/value is within a given range. That's what I'd try first.

http://www.techonthenet.com/excel/formulas/if_nested.php
 
Last edited:
johnbryanpeters

johnbryanpeters

Hero
Sep 27, 2001
36,690
18,057
Making moss sad in New Haven, Vermont
ButtersNZ said:
So you want the equation to select the price based on the range of dates? As there is no overlap between your dates, perhaps it would help to convert the dates to numbers, create a range, then use the nested "IF function" to specify the price IF the date/value is within a given range. That's what I'd try first.

http://www.techonthenet.com/excel/formulas/if_nested.php
Click to expand...
Dates are represented as real numbers. All else is formatting. e.g. 5/24/2014 is stored as 41,783.0.
 
binary visions

binary visions

The voice of reason
Jun 13, 2002
22,149
1,250
NC
Ehm...

I just tested this and it seems to work:

=INDEX(Sheet1!$A$2:$O$57,MATCH(1,(Sheet1!$A$2:$A$57=Sheet2!$A2)*(Sheet1!$N$2:$N$57<=Sheet2!F1)*(Sheet1!$O$2:$O$57>=Sheet2!F1),0),10)

edit: hit control shift enter after pasting into the cell, not just enter... it's a custom array.

Explanation:

INDEX(Sheet1!$A$2:$O$57,
INDEX looks up a row and column in an array, so I'm saying the INDEX array is Sheet1 - A2:O57

MATCH(1,(Sheet1!$A$2:$A$57=Sheet2!$A2)*(Sheet1!$N$2:$N$57<=Sheet2!F1)*(Sheet1!$O$2:$O$57>=Sheet2!F1),0)
The row for the array will be: MATCH "1" (true) based on three criteria - the SKU, greater than start date, less than end date, return an exact match ("0")

,10)
The column for the array will be: 10 (Column J = price)

 
binary visions

binary visions

The voice of reason
Jun 13, 2002
22,149
1,250
NC
Oh, and I forgot to mention... when you paste that, you have to hit control-shift-enter instead of just enter because it's a custom array. Once you have pasted it once and done the ctrl-shift-enter, it'll let you copy and paste normally.
 
ALEXIS_DH

ALEXIS_DH

Tirelessly Awesome
Jan 30, 2003
6,151
798
Lima, Peru, Peru
binary visions said:
Oh, and I forgot to mention... when you paste that, you have to hit control-shift-enter instead of just enter because it's a custom array. Once you have pasted it once and done the ctrl-shift-enter, it'll let you copy and paste normally.
Click to expand...

It´s alive!!!!!!!!! It worked.
somebody rep this guy for me, please....
 
binary visions

binary visions

The voice of reason
Jun 13, 2002
22,149
1,250
NC
ALEXIS_DH said:
It´s alive!!!!!!!!! It worked.
somebody rep this guy for me, please....
Click to expand...
Hope you don't have any overlapping dates... I'm not sure how it will react to that, might just choose an arbitrary one/the first one it comes across, if it doesn't straight out cough and die.

Glad it worked for ya', though. I spent so much time with Excel when I worked for a bankruptcy trustee, but my skills are now rusty and I like dragging them out occasionally :thumb:
 
You must log in or register to reply here.
Top Bottom