Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a cell to give a result based on the month we are in?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a cell to give a result based on the month we are in?
gives you the current month =MONTH(TODAY()) -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562223 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a cell to give a result based on the month we are in?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I get a cell to give a result based on the month we are in?
Oh, something I just noticed:
Jan Feb Mar April May June July The format needs to be the same throughout: Either all a Jan.....Feb.....Mar.....Apr.....May.....Jun.....Ju l Or, all a 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add amount to a cell based on category and month | New Users to Excel | |||
HELP| populate cell with particular value based on multiple condit | Excel Worksheet Functions | |||
How do I set a cell value based on a formula in another cell? | Excel Discussion (Misc queries) | |||
Referencing data in different worksheet based on month name | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |