[Solved] Formulas: Ongoing % formula

  • 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


    You ain't seen me......right!

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

    In cell M5 I placed the following formula

    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

    Edit: you could, of course use the following formula to cut out the use of M5

    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)

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

    in M7

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

    See attached


    There are three types of people in this world.
    Those who can count and those who can&#039;t.

  • 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


    You ain't seen me......right!

  • Give this a try:



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!