[Solved] Formulas: Ongoing % formula

XLDave · Jul 15th 2003

I use a simple spreadsheet to track my sales people. It displays their sales figures as follows.

Monthly turnover target in Rows A4:L4
Actual sales figures in Rows B4:L4
Monthly % versus target in Rows C4:L4
Annual YTD % in Rows D4:L4
The columns are headed by month January to December.

In M4:M7 is the summation of the relevant row. However, I need a simple formula that will display the YTD % in Cell M7. Because there are no sales for the months to come in the year then it calculates incorrectly. I know that the correct figure is shown in the relevant cell of Row D4:L4 but my boss needs to see it in the summation column.

Is there a formula that will calculate the % YTD and recognize that there are blank months and that we are only part way through the sales year? For example a seller that has sold £150,000 against an annual target of £200,000 is 75% of his target and someone who has sold £210,000 is 105% with several months still to come.

I have attached a small sample of the sheet.

All help gratefully received

Thanks in advance

Dave

Will Riley · Jul 15th 2003

I can't download your sheet, but I tried something similar.

In cell M5 I placed the following formula
=MONTH(TODAY())

In cell M4, I placed the following formula
=OFFSET(A5,0,M6-1) format as %

This gives 129% for the guy who's done 150,000 of an annual 200,000 budget as at July.... (i.e. it's YTD 150,000/116,666 = 129%)

He is still 75% of full year budget

My formulas in cells M1,M2,M3 are
M1 =SUM(A2:L2)
M2 =SUM(A3:L3)
M3 =M3/M2 (formatted as %)

Hope this helps

Welcome to Ozgrid
:biggrin:

Edit: you could, of course use the following formula to cut out the use of M5
=OFFSET(A5,0,MONTH(TODAY())-1)

I just did that to illustrate that =Month(Today()) returns the value of 7 (as in month 7) and is used in the OFFSET formula to determin the column offset from January (i.e. col A)

Neale · Jul 15th 2003

Hi
I've done it a but different to Will. (More complicated)

in M7
=SUM(A5:OFFSET(A5,0,COUNTIF(A5:L5,"<>0")-1))/SUM(A4:OFFSET(A4,0,COUNTIF(A5:L5,"<>0")-1))

Check out row 8 for an easier way to the row 7 formula.

See attached

HTH

XLDave · Jul 15th 2003

Okay, the formula that Will gave doesn't appear to work because the cell references are wrong. It looks like something I could understand if I could see it working for real.

I have attached an actual portion of my sheet and the cell references are identical.

If you could rewrite the formula with Month, today etc all contained in one cell that would be great.

The formula that Neale listed seems to work but if I am honest I cannot understand it and could therefore never replicate it in a real life scenario - hope that makes sense ??

Many thanks

Dave

Will Riley · Jul 15th 2003

Thanks for the file.

I have sent you a solution. :yes:

2rrs · Jul 15th 2003

Quote

Originally posted by daveray
Okay, the formula that Will gave doesn't appear to work because the cell references are wrong. It looks like something I could understand if I could see it working for real.

I have attached an actual portion of my sheet and the cell references are identical.

If you could rewrite the formula with Month, today etc all contained in one cell that would be great.

The formula that Neale listed seems to work but if I am honest I cannot understand it and could therefore never replicate it in a real life scenario - hope that makes sense ??

Many thanks

Dave

Display More

Give this a try:

=AVERAGE(OFFSET(B9,0,0,1,MONTH(NOW())))

2rrs

[Solved] Formulas: Ongoing % formula

Participate now!

Share