Macro/Formula percentage problem

[Lost-in-Japan]

Hi all

I've been tasked with creating a sheet I have no chance at creating
Any solutions or advice on where to concentrate would be greatly appreciated

Problem
Every Monday I check the disk space of over 180 servers (up to 7 drives on some)
I need a sheet to paste the data in for 4 weeks that will

1. create a percentage of free spac
2. after the second weeks data is entered
a. retain the column with the percent of free space from week
b. show a new value of percentage of free space for week2 (week 3 and week 4
c. average out the amount/percent of space used each week
3. make a prediction of when certain markers are meet (7% free space and 3% free space) based on step 2 outputed into weeks (will reach 7% free space in 5.23 weeks...

My template is based on a 4 week cycle (1 tab for every 4 weeks) and looks like this
row1 is all headings (server name, total space, space used, %.....
(A2) sevrer nam
(B2) total C-drive capacit
(C2) week1 datasiz
(D2) week2 datasiz
(E2) week3 datasiz
(F2) week4 datasiz
(G2) total D-drive capacit
(H2) week1 datasiz
(I2) week2 datasiz
(J2) week3 datasiz
(K2) week4 datasiz
(J2) C-drive free space (in %) week
(L2) C-drive free space (in %) week
(M2) C-drive free space (in %) week
(N2) C-drive free space (in %) week
(O2) D-drive free space (in %) week
(P2) D-drive free space (in %) week
(Q2) D-drive free space (in %) week
(R2) D-drive free space (in %) week
(S2) C-drive average percent of space used each week (starts week 2
(T2) C-drive estimated time before disk has 7% free space (weeks to 2 decimals
(U2) C-drive estimated time before disk has 3% free space (weeks to 2 decimals
(V2) D-drive average percent of space used each week (starts week 2
(X2) D-drive estimated time before disk has 7% free space (weeks to 2 decimals
(Y2) D-drive estimated time before disk has 3% free space (weeks to 2 decimals

rows 3 to 188 are the values for different servers

Also, for the real sheet, I will need to list 7 drives, more than half of them will have no base or incremental values, but i want all the info to line up for easy referance

The most I've ever done before is to use the auto-sum on a simple budget worksheet

If anyone has the time/interest to figure this out, I can send a copy of the template and also a sample of what I will be pasting into it

Otherwise, without learning everything about excel, what features should I be studying to accomplish this task

Thank you for reading this far
Jaso

 

[Lost-In-Japan]

Hi again,

After re-reading my post I realized how simple the first part was.
I'm now stuck on 2c.
I can get 4 weeks of percentages (L2 through S2) and I can get an average based on those 4 weeks (T2) but what I can't do is have (T2) dynamicaly update.

I'm using "=(L2+M2+N2+O2)/4" which works fine for the total after 4weeks, but until that time, anything in the T-column is incorrect as it is looking at all 4 columns and then dividing by 4.

Is there a way (within a single cell) to do something like this:

If value exists for L2 do nothing
If value exists for L2 and M2 then "=(L2+M2)/2"
If value exists for L2 and M2 and N2 then "=(L2+M2+N2)/3"
If value exists for L2 and M2 and N2 and O2 then "=(L2+M2+N2+O2)/4"

So that the correct average is always displayed?

TIA,
Jason

----- Lost-in-Japan wrote: -----

Hi all,

I've been tasked with creating a sheet I have no chance at creating.
Any solutions or advice on where to concentrate would be greatly appreciated!

Problem:
Every Monday I check the disk space of over 180 servers (up to 7 drives on some).
I need a sheet to paste the data in for 4 weeks that will.

1. create a percentage of free space
2. after the second weeks data is entered:
a. retain the column with the percent of free space from week 1
b. show a new value of percentage of free space for week2 (week 3 and week 4)
c. average out the amount/percent of space used each week.
3. make a prediction of when certain markers are meet (7% free space and 3% free space) based on step 2 outputed into weeks (will reach 7% free space in 5.23 weeks...)

My template is based on a 4 week cycle (1 tab for every 4 weeks) and looks like this:
row1 is all headings (server name, total space, space used, %.....)
(A2) sevrer name
(B2) total C-drive capacity
(C2) week1 datasize
(D2) week2 datasize
(E2) week3 datasize
(F2) week4 datasize
(G2) total D-drive capacity
(H2) week1 datasize
(I2) week2 datasize
(J2) week3 datasize
(K2) week4 datasize
(L2) C-drive free space (in %) week1
(M2) C-drive free space (in %) week2
(N2) C-drive free space (in %) week3
(O2) C-drive free space (in %) week4
(P2) D-drive free space (in %) week1
(Q2) D-drive free space (in %) week2
(R2) D-drive free space (in %) week3
(S2) D-drive free space (in %) week4
(T2) C-drive average percent of space used each week (starts week 2)
(U2) C-drive estimated time before disk has 7% free space (weeks to 2 decimals)
(V2) C-drive estimated time before disk has 3% free space (weeks to 2 decimals)
(X2) D-drive average percent of space used each week (starts week 2)
(Y2) D-drive estimated time before disk has 7% free space (weeks to 2 decimals)
(Z2) D-drive estimated time before disk has 3% free space (weeks to 2 decimals)

rows 3 to 188 are the values for different servers.

Also, for the real sheet, I will need to list 7 drives, more than half of them will have no base or incremental values, but i want all the info to line up for easy referance.

The most I've ever done before is to use the auto-sum on a simple budget worksheet!

If anyone has the time/interest to figure this out, I can send a copy of the template and also a sample of what I will be pasting into it.

Otherwise, without learning everything about excel, what features should I be studying to accomplish this task?

Thank you for reading this far!
Jason

 

[Frank Kabel]

Hi
I would suggest you take a look at piot tables for this (group them by
week). See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I've been tasked with creating a sheet I have no chance at creating.
Any solutions or advice on where to concentrate would be greatly appreciated!

Problem:
Every Monday I check the disk space of over 180 servers (up to 7 drives on some).
I need a sheet to paste the data in for 4 weeks that will.

1. create a percentage of free space
2. after the second weeks data is entered:
a. retain the column with the percent of free space from week 1
b. show a new value of percentage of free space for week2 (week 3 and week 4)
c. average out the amount/percent of space used each week.
3. make a prediction of when certain markers are meet (7% free space

and 3% free space) based on step 2 outputed into weeks (will reach 7%
free space in 5.23 weeks...)

My template is based on a 4 week cycle (1 tab for every 4 weeks) and looks like this:
row1 is all headings (server name, total space, space used, %.....)
(A2) sevrer name
(B2) total C-drive capacity
(C2) week1 datasize
(D2) week2 datasize
(E2) week3 datasize
(F2) week4 datasize
(G2) total D-drive capacity
(H2) week1 datasize
(I2) week2 datasize
(J2) week3 datasize
(K2) week4 datasize
(J2) C-drive free space (in %) week1
(L2) C-drive free space (in %) week2
(M2) C-drive free space (in %) week3
(N2) C-drive free space (in %) week4
(O2) D-drive free space (in %) week1
(P2) D-drive free space (in %) week2
(Q2) D-drive free space (in %) week3
(R2) D-drive free space (in %) week4
(S2) C-drive average percent of space used each week (starts week 2)
(T2) C-drive estimated time before disk has 7% free space (weeks to 2 decimals)
(U2) C-drive estimated time before disk has 3% free space (weeks to 2 decimals)
(V2) D-drive average percent of space used each week (starts week 2)
(X2) D-drive estimated time before disk has 7% free space (weeks to 2 decimals)
(Y2) D-drive estimated time before disk has 3% free space (weeks to 2 decimals)

rows 3 to 188 are the values for different servers.

Also, for the real sheet, I will need to list 7 drives, more than

half of them will have no base or incremental values, but i want all
the info to line up for easy referance.

The most I've ever done before is to use the auto-sum on a simple budget worksheet!

If anyone has the time/interest to figure this out, I can send a copy

of the template and also a sample of what I will be pasting into it.

Otherwise, without learning everything about excel, what features

should I be studying to accomplish this task?

 

[DJONES]

You can nest all the IF statements in to the same cell. As long as yo
do them in the correct order it will work fine.

If(a2=""),"",IF(a3>0),(a2+a3)/2,IF(a4>0),(a2+a3+a4)/4ec