Macro/Formula percentage problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Lost-in-Japan said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
of the template and also a sample of what I will be pasting into it.
Otherwise, without learning everything about excel, what features
Click to expand...
should I be studying to accomplish this task?
 
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
 
Back
Top