Very tricky question here, hope you can help
09/10/2001 10/10/2001
Apple 1 week 1 2
1 month 2 3
Orange 1 week 2 2
1 month 1 3
Days
orange 09/10/2005 18/10/2005 9
ANS 1.066666667
what I'm looking for is a formula that allows me to look up a large table of data that searches based on two variables, ie the fruit and the date.
once it finds this then it has to recognise that the number of days mean it has to use two figures
e.g using the above example orange would bring it to A5, the date would bring it to the C column
it would therefore fall in between 2 periods therefore it would take in all of the C1 and a % of the difference between the two rates to take into account the two days outside the 1week period
09/10/2001 10/10/2001
Apple 1 week 1 2
1 month 2 3
Orange 1 week 2 2
1 month 1 3
Days
orange 09/10/2005 18/10/2005 9
ANS 1.066666667
what I'm looking for is a formula that allows me to look up a large table of data that searches based on two variables, ie the fruit and the date.
once it finds this then it has to recognise that the number of days mean it has to use two figures
e.g using the above example orange would bring it to A5, the date would bring it to the C column
it would therefore fall in between 2 periods therefore it would take in all of the C1 and a % of the difference between the two rates to take into account the two days outside the 1week period