automate a formula

oddworld

Active Member
Joined
May 31, 2005
Messages
250
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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.
 
Upvote 0
Hi

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


Tony
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,254
Messages
6,135,490
Members
449,942
Latest member
Gitad

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