automate a formula

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
Hi all can somebody please give me a hand, i am trying to automate a formula that is changed each month.

I have a sheet with strength (no of pers) in column b
i have no of separations in column c

in a1 i have the following formula - =SUM(C202:C213)/((((B201+B213)/2)+B202+B203+B204+B205+B206+B207+B208+B209+B210+B211+B212)/12)

c202:c213 12 months of seps data/2
b201+b213 is the first and last month over 13 months
+b202 through b212 is the remaining 11 months strength
This formula gives me a rolling separation percentage.

I need vba code to automatically select the last 13 months on data in column b and last 12 months data in column c and apply my current formula, this will save me to change the range each month , more importantly i will leave no room for error.

any ideas would be appreciated.
Cheers
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi
Try

Code:
Sub aaa()
  lastrow = Cells(Rows.Count, 2).End(xlUp).Row
  Range("A1").Formula = "=SUM(C" & lastrow - 11 & ":C" & lastrow & ")/(((AVERAGE(B" & lastrow - 12 & ",B" & lastrow & ")+SUM(B" & lastrow - 11 & ":B" & lastrow - 1 & "))/12))"
End Sub

I've compressed the formula a bit to make life easier.

Tony
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

just one last question, your code is working great on sheets that have the data in columns, how would i change your code to read similar data but in rows ie the strength is in row 53 and the separations are in row 61.
I don't want to transpose the data if i can hepl it.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

I'm feeling lazy. What is your formula for the transposed data.


Tony
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

in A1 I HAVE =SUM(Q28:AB28)/(((AVERAGE(P27,AB27)+SUM(Q27:AA27))/12))
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try

Code:
Sub bbb()
  lastcol = Cells(27, Columns.Count).End(xlToLeft).Column
  Range("A1").Formula = "=SUM(r28c" & lastcol - 11 & ":r28c" & lastcol & ")/(((AVERAGE(r27c" & lastcol - 12 & ",r27c" & lastcol & ")+SUM(r27c" & lastcol - 11 & ":r27c" & lastcol - 1 & "))/12))"
  Range("A1").Formula = WorksheetFunction.Substitute(Range("r24").Formula, "$", "")
End Sub


Tony
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

Thank you for your time and effort, i couldn't get your code to work however the following code seems to work

Sub test()
LastCol = Cells(27, Columns.Count).End(xlToLeft).Column
Range("h20").Formula = "=SUM(" & Cells(28, LastCol - 11).Resize(, 12).Address & ") / (((AVERAGE(" & Cells(27, LastCol - 12).Resize(, 12).Address & ")+ SUM(" & Cells(27, LastCol - 11).Resize(, 11).Address & "))/12)) "
End Sub

cheers
 

Forum statistics

Threads
1,136,261
Messages
5,674,702
Members
419,520
Latest member
Jennifer4Dillon

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