how to insert formula to calculate average

kcube17

New Member
Joined
Jan 7, 2013
Messages
15
hi
I have months in rows and names in columns. apr to sep achievement last and apr to aug achievement this year. now i want to give a target for september so that the year till date (YTD sep) growth will be 5% more than YTD Growth of aug. I am unable to find a formula. please help

NamesApr'13May'13June'13July,13Aug'13Sep'13TotalApr'14May'14June'14July,14Aug'14Sep'14TotalYTD Aug GrYTD Sep Gr
A158587345871526-4%-24%
B25141518415251731%-6%
C58232828328582630%-7%
D4545452754545235%-15%
E58515832158582713%-16%
F676126744126767383%-14%
G728282292827221-22%-28%
H87444734447873011%-12%

<colgroup><col span="7"><col><col span="6"><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

kcube17

New Member
Joined
Jan 7, 2013
Messages
15
What is the formula you use?

Is it YTD growth YoY?
its YTD growth. for example for A I want to give a target so that YTD Sep Growth will become 1% form -4%. there are thousands of rows like that so is there any formula to do it
 

tatoon

New Member
Joined
Jul 31, 2014
Messages
7

ADVERTISEMENT

I think this is more a math issue than excel.

In row A:

YTD Aug Gr is 4% --> 1 - (26/27) being 26 the YTD (Apr-Aug 14) and 27 the YTD (Apr-Aug 13)

Sep'14 --> (1 - YTD Sep Gr)*(YTD Apr-Sep 13)
 

kcube17

New Member
Joined
Jan 7, 2013
Messages
15
thanks for rep but its nor working. what i want is if i put a number in sep'14, YTD Sep Gr should become 1% (5% increment over aug gr)
 

tatoon

New Member
Joined
Jul 31, 2014
Messages
7

ADVERTISEMENT

can you attach a file with the table, and in which cell you want the formula and what it should appear there?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
how to upload file?
You can't, but you can show small screen shots like I have here. My signature block below has several suggestions for how to do that and for where to test them.

See if this is what you want.

Excel Workbook
ABCDEFGHIJKLMNOPQ
1NamesApr'13May'13June'13July,13Aug'13Sep'13TotalApr'14May'14June'14July,14Aug'14Sep'14TotalYTD Aug GrYTD Sep Gr
2A15858734587158.3434.34-4%1%
3B25141518415257.4824.4831%36%
4C582328283285811.837.830%35%
5D45454527545456.729.75%10%
6E585158321585810.7637.7613%18%
7F6761267441267679.5247.523%8%
8G72828229282723.0724.07-22%-17%
9H87444734447879.4439.4411%16%
Target
 

kcube17

New Member
Joined
Jan 7, 2013
Messages
15
can you attach a file with the table, and in which cell you want the formula and what it should appear there?

please download from here

https://www.mediafire.com/?59zgw1f0eez9zqw


You can't, but you can show small screen shots like I have here. My signature block below has several suggestions for how to do that and for where to test them.

See if this is what you want.

Target

ABCDEFGHIJKLMNOPQ
1NamesApr'13May'13June'13July,13Aug'13Sep'13TotalApr'14May'14June'14July,14Aug'14Sep'14TotalYTD Aug GrYTD Sep Gr
2A15858734587158.3434.34-4%1%
3B25141518415257.4824.4831%36%
4C582328283285811.837.830%35%
5D45454527545456.729.75%10%
6E585158321585810.7637.7613%18%
7F6761267441267679.5247.523%8%
8G72828229282723.0724.07-22%-17%
9H87444734447879.4439.4411%16%

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:53px;"><col style="width:93px;"><col style="width:111px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
H2=SUM(B2:G2)
N2=SUM($B2:G2)*(P17+1.05)-SUM($I2:M2)
O2=SUM(I2:N2)
P2=(SUM($I2:M2)-SUM($B2:F2))/SUM($B2:F2)
Q2=(SUM($I2:N2)-SUM($B2:G2))/SUM($B2:G2)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thanks a lot. what is there in P17
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
Thanks a lot. what is there in P17
Sorry, that was a mistake :oops:
I'll try again.

Excel Workbook
ABCDEFGHIJKLMNOPQ
1NamesApr'13May'13June'13July,13Aug'13Sep'13TotalApr'14May'14June'14July,14Aug'14Sep'14TotalYTD Aug GrYTD Sep Gr
2A15858734587158.440734.441-4%1%
3B25141518415257.438524.43831%36%
4C582328283285811.837.830%35%
5D45454527545456.577329.5775%10%
6E585158321585810.637.613%18%
7F6761267441267679.389247.3893%8%
8G72828229282723.005624.006-22%-17%
9H87444734447879.477839.47811%16%
Target
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top