I want to calculate the % difference between actual and goal for the month we
are in. i.e. J1=I1/C1(when we are in Feb), or J1=I1/E1(when we are in
April). In other words, the answer for J1 will reflect whatever month we are
currently in. If you open up the spreadsheet in July it will be J1=H1/I1. I
think the formula may involve the TODAY function but I am not sure.


A B C D E F G H I
J
Jan Feb Mar April May June July Goal % Diff
1 Product 1
2 Product 2

gives you the current month

=MONTH(TODAY()

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...fo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56222

Hi!

If you month headers are in row 1 then the numeric data is is row 2.

Also, which is which:

=I1/C1
=I1/E1
=H1/I1

Is it goal/value or value/goal ?

Anyhow:

For goal/value, month headers in row 1:

=IF(I2="","",I2/INDEX(B2:H2,MATCH(TEXT(TODAY(),"mmm"),B1:H1,0)))

Biff

"SHBmgr" <(E-Mail Removed)> wrote in message
news:05593577-6E85-4CF9-B79D-(E-Mail Removed)...
>I want to calculate the % difference between actual and goal for the month
>we
> are in. i.e. J1=I1/C1(when we are in Feb), or J1=I1/E1(when we are in
> April). In other words, the answer for J1 will reflect whatever month we
> are
> currently in. If you open up the spreadsheet in July it will be J1=H1/I1.
> I
> think the formula may involve the TODAY function but I am not sure.
>
>
> A B C D E F G H I
> J
> Jan Feb Mar April May June July Goal % Diff
> 1 Product 1
> 2 Product 2

Oh, something I just noticed:

>Jan Feb Mar April May June July

The format needs to be the same throughout:

Either all are:

Jan.....Feb.....Mar.....Apr.....May.....Jun.....Jul

Or, all are:

January...February...March...April...May...June...July

Which ever way you choose, adjust this portion of the formula as
appropriate:

TEXT(TODAY(),"mmm")

For the short month name use the above, for the long month name use:

TEXT(TODAY(),"mmmm")

Biff

"Biff" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi!
>
> If you month headers are in row 1 then the numeric data is is row 2.
>
> Also, which is which:
>
> =I1/C1
> =I1/E1
> =H1/I1
>
> Is it goal/value or value/goal ?
>
> Anyhow:
>
> For goal/value, month headers in row 1:
>
> =IF(I2="","",I2/INDEX(B2:H2,MATCH(TEXT(TODAY(),"mmm"),B1:H1,0)))
>
> Biff
>
> "SHBmgr" <(E-Mail Removed)> wrote in message
> news:05593577-6E85-4CF9-B79D-(E-Mail Removed)...
>>I want to calculate the % difference between actual and goal for the month
>>we
>> are in. i.e. J1=I1/C1(when we are in Feb), or J1=I1/E1(when we are in
>> April). In other words, the answer for J1 will reflect whatever month we
>> are
>> currently in. If you open up the spreadsheet in July it will be
>> J1=H1/I1. I
>> think the formula may involve the TODAY function but I am not sure.
>>
>>
>> A B C D E F G H I
>> J
>> Jan Feb Mar April May June July Goal % Diff
>> 1 Product 1
>> 2 Product 2
>
>