If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro/Formula percentage problem
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" schrieb im Newsbeitrag news 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? Thank you for reading this far! Jason |
#2
|
|||
|
|||
Macro/Formula percentage problem
You can nest all the IF statements in to the same cell. As long as you
do them in the correct order it will work fine. If(a2=""),"",IF(a30),(a2+a3)/2,IF(a40),(a2+a3+a4)/4ect --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|