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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
can you attach a file with the table, and in which cell you want the formula and what it should appear there?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top