Thank-you Narayan, both my vlookup and match formula works perfect now.
as for formula 3, because my date range from cell R7:CT7 is 1st of the month so the existing formula doesn't recognise the date format in column E so I had to insert column F.
Is there a formula that recognise the date range...
Hi
I'm having problems with my formula's and hope someone can guide me.
Formula 1: Please refer to attachment- Worksheet "Graph Data" - I've inserted a vlookup & match formula from range C2:CT29 but it's not picking up the values. don't know what I'm missing.
Formula 2: Worksheet "Graph Data"...
Hi
Does anyone know if there is a formula to pick up the amount and spread it base on the order date. Like the example below:
Order Date Amount Jan14 Feb14 Mar14 Apr14 May14
Feb14 50,000 50,0000
May14 12,000...
Hi Narayan
Thanks for your responds. However I tried the formula but it's not giving me the answer I want
=30/06/2014+(52*7) = 364.002
I actually want to 52 weeks Prior to 30/06/2014 will return what date.
For Example Current date is 02/05/2014, deduct 2 weeks, answer is 18/04/2014
Hi
I have a problem with excel vlookup, when I insert a vlookup formula in my spreadsheet in Cell B3:B7 It will just pick up first answer all the way through. When I double click each indivual cell the answer will change to the correct one.
Please refer to below example: B4 should be 15...
Hi Narayan
Thank-you...
I came across another problem with my data.
What if the range consist of more than one variable.
Eg "1", "0.5" or "0.25"
I'm so sorry for not picking this up earlier.
cheers
jrl1208
Hi Narayan
Yes I would like a formula to cater for gaps in between months. As per your example, 1s between March and June , and then between September and November, I want March to be the start date and November to be the end date.
Thank-you for your help.
Hi Narayan
Thank-you so much!!! Yes this is the formula I'm after. This will save me so much time. Thank-you once again!!!!!!!!!!!!!
But just out of curiosity, what if "1" is not consecutive, there is gaps in between.
Is there a formula that will do the trick?
jrl1208
Hi SirJb7
I've manage to download the file and had a look.
Unfortunately the formula you wrote is not exactly what I want. The formula will spread the "1" across the months but that is not what I want.
Eg. The "1" is already spread in the range C2:H4. I want a formula to identify which...
Hi Nacky
Thank-you for the clarification.
However I wasn't clear in what I wanted. So let me try explaining it again.
Eg.
Within the cell range I have "1" indicating I have resource for that particular month.
From C2:H2 I have resource starting in Feb12 and ending in Mar12
I want to know...
Hi
I would like to know if there is a formula that will give pick up the start and end date as per the below table. Thanks.
Start End Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12
Feb-12 Apr-12 1 1 1
Jan-12 Jun-12 1 1 1 1 1 1
Mar-12 Apr-12 1 1