Stop forecast formula

[Erkjerk]

Hello,

Just seeing if I can get a little help. So I have a forecast formula =C2/DAY(TODAY())*31 The problem is it doesn't stop at the end of the month at the 31st, it will just keep calculating so the forecast will show some ridiculous number the day after the 31st. I hope this makes sense.

 

[Skybluekid]

The below will correct the month issue:

Book1
	C	D	E	F
2
3	0		1	31
4	0		2	28
5			3	31
6			4	30
7			5	31
8			6	30
9			7	31
10			8	31
11			9	30
12			10	31
13			11	30
14			12	31
Sheet1

Cell Formulas
Range	Formula
C3	C3	=C1/DAY(TODAY())*(VLOOKUP(MONTH(TODAY()),$E$3:$F$14,2,0))
C4	C4	=C2/DAY(TODAY())*(LOOKUP(MONTH(TODAY()),{1,2,3,4,5,6,7,8,9,10,11,12},{31,28,31,30,31,30,31,31,30,31,30,31}))
F4	F4	=DAYS(EOMONTH(DATEVALUE("01/02/"&YEAR(TODAY())),0),DATEVALUE("01/02/"&YEAR(TODAY())))+1

There two ways.
1)The VLOOKUP will require a table of month numbers and days of the month to be located somewhere in the workbook(February is a calcuation as some years will be 29 days, the formula will do this automatically.

2) The Lookup is self contained as the look up value is month number today and this will bring back the number of days. However, you will have to manually check February every to see if the days are correct.

 

[Erkjerk]

Awesome, thank you. I will use the vlookup. I am assuming I would have to change the formula though because I make a new sheet at the bottom for each separate month, I don't overwrite the current sheet. So at the bottom of the excel workbook, first tab is Jan 2nd tab is Feb, 3rd is Mar, etc. Thank you.

 

[Skybluekid]

No really, you can put the months on a separate sheet to the months and then reference the VLookup to that sheet.

 

[Erkjerk]

No really, you can put the months on a separate sheet to the months and then reference the VLookup to that sheet.

Awesome, I added the formula. I appreciate the help.

 

[Skybluekid]

You very welcome

 

[Erkjerk]

Just one more question lol. Is there a reason it is pulling from E3 and F14 rather then E3 and F3 for January?

 

[Skybluekid]

With Vookup, you need to specify the lookup value(month today, which will be 1), the range where the data is held(E3:F14),which column, within the range, where the data is to be returned, in this case column 2, finally whether want an exact or approximate match, in this case exact.

Have a look at this Video, minute 49, this will explain how VLOOKUP works

 

[Erkjerk]

Okay cool thanks

 

[Skybluekid]

No problem