Predicting annual return on monthly figures

Diego Garcia · May 22nd, 2002, 02:16 AM

Each month I receive varying amounts of income. To predict the annual income I use the following formula (this row is for the first six months). Is there a shorter/easier way of doing this ?
=IF(G10>0,SUM(B10:G10),IF(F10>0,(SUM(B10:G10)/5)*6,IF(E10>0,(SUM(B10:E10)/4)*6,IF(D10>0,(SUM(B10:G10)/3)*6,IF(C10>0,((B10+C10)/2)*6,IF(B10>1,B10*6,0))))))
This formula is in H10, Jan income is entered in B10, Feb in C10 et seq.

Iain Lewis · May 22nd, 2002, 02:22 AM

The Trend function may give you what you want - it takes know dates & corresponding values & then calculates the values for unknown dates.

Diego Garcia · May 22nd, 2002, 03:42 AM

Trend is too advanced for me as I am unable to get it to return anything other than #VALUE!

Iain Lewis · May 22nd, 2002, 03:49 AM

A couple of things to check :

Have you got your known Dates & known Values exactly the same size ?

The cells that contain the dates must be in a date format else XL can't calculate duration or each time period.

Ian Mac · May 22nd, 2002, 04:00 AM

Quote:

On 2002-05-22 02:16, Diego Garcia wrote:
Each month I receive varying amounts of income. To predict the annual income I use the following formula (this row is for the first six months). Is there a shorter/easier way of doing this ?
=IF(G10>0,SUM(B10:G10),IF(F10>0,(SUM(B10:G10)/5)*6,IF(E10>0,(SUM(B10:E10)/4)*6,IF(D10>0,(SUM(B10:G10)/3)*6,IF(C10>0,((B10+C10)/2)*6,IF(B10>1,B10*6,0))))))
This formula is in H10, Jan income is entered in B10, Feb in C10 et seq.

if your cells are empty before values are placed in, i.e. they are NOT 0 use:

=(SUM(B10:G10)/COUNT(B10:G10))*6

if they have formulas or 0's use:

=SUM(B10:G10)/COUNTIF(B10:G10,">"&0)*6

Diego Garcia · May 22nd, 2002, 04:03 AM

I do not have dates in the workbook, just values in those cells in row 10

Diego Garcia · May 22nd, 2002, 04:16 AM

Ah ! the simplicity of it all. Cells are empty and that works just fine. Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 02:16 AM	#1
Diego Garcia New Member Join Date: May 2002 Location: Rhos On Sea, Wales Posts: 23	Each month I receive varying amounts of income. To predict the annual income I use the following formula (this row is for the first six months). Is there a shorter/easier way of doing this ? =IF(G10>0,SUM(B10:G10),IF(F10>0,(SUM(B10:G10)/5)6,IF(E10>0,(SUM(B10:E10)/4)6,IF(D10>0,(SUM(B10:G10)/3)6,IF(C10>0,((B10+C10)/2)6,IF(B10>1,B10*6,0)))))) This formula is in H10, Jan income is entered in B10, Feb in C10 et seq.

May 22nd, 2002, 02:22 AM	#2
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	The Trend function may give you what you want - it takes know dates & corresponding values & then calculates the values for unknown dates.

May 22nd, 2002, 03:42 AM	#3
Diego Garcia New Member Join Date: May 2002 Location: Rhos On Sea, Wales Posts: 23	Trend is too advanced for me as I am unable to get it to return anything other than #VALUE!

May 22nd, 2002, 03:49 AM	#4
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	A couple of things to check : Have you got your known Dates & known Values exactly the same size ? The cells that contain the dates must be in a date format else XL can't calculate duration or each time period.

May 22nd, 2002, 04:03 AM	#6
Diego Garcia New Member Join Date: May 2002 Location: Rhos On Sea, Wales Posts: 23	I do not have dates in the workbook, just values in those cells in row 10

May 22nd, 2002, 04:16 AM	#7
Diego Garcia New Member Join Date: May 2002 Location: Rhos On Sea, Wales Posts: 23	Ah ! the simplicity of it all. Cells are empty and that works just fine. Thanks