Using lookup function

Hi,

I am trying to code a spdsht that can be used to monitor diskspace across 
multiple servers and logical drives per server.

The data acquisition is complete and i have no problems automating the 
collection of the data using a wmi script and feeding it into excel and 
generated basic time vs diskspace charts.

What i want to create is a simple dashboard style chart that will show any 
logical drive that is below a certain threshold to provide a clear visual 
warning to the operator.

At the moment I have a simple min() function using if() to remove servers 
whose free space is great than 50%. however .... if a server recovers space 
the chart will still only show the min() value.

How can I use a lookup function to generate this chart only for the current 
days data set (Did i mention that the spdsht is archived once a month?) so 
that.

I have so far been unsucessful in creating this.

my data set looks something like this...

//server/drive,1-jan,2-jan,3-jan,
//svr1/c,65,63,40,38,
//sv1/d,80,80,80,80,
//svr2/c,20,15,5,60,

As you can see ... if i use min() then for //svr2/c it will only ever report 
as 5% free even though the following day diskspace increased to 60%

Any help greatfully accepted.

G.
0
Gary103 (266)
7/11/2005 1:45:04 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
457 Views

Similar Articles

[PageSpeed] 3

Hi!

A combination Vlookup/Match should work:

=VLOOKUP(server,table_array,MATCH(date,lookup_array,0))

Biff

"Gary" <Gary@discussions.microsoft.com> wrote in message 
news:C0FB1592-E059-4260-8F1F-B114F74273C9@microsoft.com...
> Hi,
>
> I am trying to code a spdsht that can be used to monitor diskspace across
> multiple servers and logical drives per server.
>
> The data acquisition is complete and i have no problems automating the
> collection of the data using a wmi script and feeding it into excel and
> generated basic time vs diskspace charts.
>
> What i want to create is a simple dashboard style chart that will show any
> logical drive that is below a certain threshold to provide a clear visual
> warning to the operator.
>
> At the moment I have a simple min() function using if() to remove servers
> whose free space is great than 50%. however .... if a server recovers 
> space
> the chart will still only show the min() value.
>
> How can I use a lookup function to generate this chart only for the 
> current
> days data set (Did i mention that the spdsht is archived once a month?) so
> that.
>
> I have so far been unsucessful in creating this.
>
> my data set looks something like this...
>
> //server/drive,1-jan,2-jan,3-jan,
> //svr1/c,65,63,40,38,
> //sv1/d,80,80,80,80,
> //svr2/c,20,15,5,60,
>
> As you can see ... if i use min() then for //svr2/c it will only ever 
> report
> as 5% free even though the following day diskspace increased to 60%
>
> Any help greatfully accepted.
>
> G. 


0
biffinpitt (3172)
7/11/2005 5:21:58 AM
Reply:

Similar Artilces:

MOD
when i apply the MOD function in a number>100000000000 returns #NUM -- Message posted from http://www.ExcelForum.com Hi this is one annoying problem of Excel's MOD implementation. It can't handle large numbers -- Regards Frank Kabel Frankfurt, Germany > when i apply the MOD function in a number>100000000000 returns #NUM! > > > --- > Message posted from http://www.ExcelForum.com/ Here's a good kb article: XL: MOD() Function Returns #NUM! Error Value http://support.microsoft.com/default.aspx?scid=kb;EN-US;119083 -- Dana DeLouis Using Windows XP &...

Using a Publisher Template in Word
I've got my company letterhead created in Publisher and it is printed it off to create a set of company-headed notepaper. When I come to write a letter I enter the text into Word using a word template for the correct margins, etc and then print onto the pre-printed letterhead. Increasingly I want to be able to create the same letter on the company letterhead but print to pdf so I can send the letter as a file attach. It seems a chore to do it all in Publisher and I don't seem to have an option to create a Word template from Publisher Can anyone tell me the best wa...

Median If Function
Hi! I am working on a statistics report, and I need to find the median of a percentage increase, decrease, and no change. Would these be the formulas I need to use? =MEDIAN(IF(C160:CI160,">0")) for increase =MEDIAN(IF(C160:CI160,"<0")) for decrease =MEDIAN(IF(C160:CI160,"0")) for no change Thanks so much for your help! ~Allison Try =MEDIAN(IF(C160:CI160>0,C160:CI160)) etc., which is an array formula -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "allyrose79" <allyros...

string function to wrap with designated delimiter
I need a string function that can wrap a string into an array of substrings. The function should take a delimiter character and a max length parameter and return the array of strings that minimizes the number of array elements, each of which would terminate with that delimiter, except when the max length requires a break in the middle of the string not at a delimiter, and allowing that the final element might not terminate with a delimiter character. For example given the delimiter '\', a max length of 30 and the string 'C:\Documents and Settings\Larry\My Documents\...

Can an update query be used to update/produce a query?
I'd like to use an update query in a chain of nested queries. Is this possible? In this case the update query will not be updating a table, it will be changing certain values from the preceding query before proceeding with the next query in the chain. My particular need is to substitute a dummy value (-9999) for all null values in a final table which my query chain makes (the final query in the chain is a make-table query which produces the table). I have to make this substitution to several columns of the final table before exporting the table for use in some other software. I...

Printing A2 size using A4 paper size
I have created a timetable in a worksheet (Page Setup A4, Landscape).. I wish to blow it up to A2 size (which is equivalent to four A4 shee joined together). As my printer (HP deskjet 5550) restrictions only permit upto A4 siz printing, how may I setup the worksheet to be printed onto four A sheets so it merge into an A2 size sheet -- Message posted from http://www.ExcelForum.com ...

Can I use AVERAGEIFS?
> Example: > >> > A B C D > >> > 1 01/10/2009 Cancelled John, Steven 4 > >> > 2 01/09/2009 Reported John, Steven 2 > >> > 3 20/10/2009 Reported John, Darren N/A > >> > 4 12/10/2009 Reported John, Darren, Steven 2 > >> > 5 14/10/2009 Reported Darren, Steve...

custom function-code for percentage
I am new to VBA. However I have created a custom function using VBA and the code is detailed below. Public Function PLF(UnitsGenerated, PlantCapacity) ' This user defined function provides the PLF of the Plant in two decimals PLF = (UnitsGenerated / PlantCapacity) * 8.76 PLF = Application.Round(PLF, 2) End Function The result of this custom function is shown in two digits. But I want a percent symbol along with this result. Can anyone would suggest me the code to be added for this for which I thank you in advance. seenu Format the cell as a percentage and change ...

Using excel to manage event
Hello! I am an event manager currently working to set up a system that can handle the planning process of a sporting event. Background: I am one of three people left in the run for an event director job for one of the largest soccer clubs in Scandinavia. I received the following task for next interview: Next week I have to present an efficient management system, that can systematize the processes of planning, coordinating and managing the club's sporting event (soccer match). There are generally 20.000 people visiting the game, and there 600 people (including volunteers) workin...

extract data from combo box n use it in SQL
not sure if i got the right forum I have a form created by VBA, and on the form I have a combo box where the user can specify the month and year. I need to retrieve data from a database using SQL but i require the input from the combo box to be part of my condition in my SQL statement to retrieve the data. so any idea wat is the syntax for writing a SQL statement with the consideration of the input from the combo box? right now i have 2 combo box. 1 holding the months, the other holding the year SELECT b.OrgID, a.OrgName,eventDate, FROM tblossevent AS b, tbOrg AS a WHERE eventdate BETWEEN...

Totalling seperate cells from a HLOOKUP function
is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it re...

Outlook Express Set Up using Yahoo
I use Yahoo to manage my e mails and want to know if anyone can advise me on the ISP server information required by the set up wizard for Yahoo so i can set up Outlook Express please? "michael.smiffy" <michael.smiffy@discussions.microsoft.com> wrote in message news:D2696B20-8C4B-462E-BACE-8159A006FDBE@microsoft.com... > I use Yahoo to manage my e mails and want to know if anyone can advise me > on > the ISP server information required by the set up wizard for Yahoo so i > can > set up Outlook Express please? Wrong group. This one is for .NET...

missing message when using TRACE output in time-critical apps
Hi, every. I'm using visual c++ 6.0 and debug my apps in DEBUG mode. I have a lot of data to trace out in output window. Which I call "TRACE" in a LOOP, they can plot the data in caculation progress. But I check the output window, I found that some messages lost.( the trace output is no more than 100 chracters each time) These messages woundn't be lost when I STEP into the LOOP or I delete some TRACE statements in the LOOP body. Does anyone have heared this kind of problems? Does it means that I could only use some Logfile class to record my data and avoid using TRACE? Tha...

Problem using SUMPRODUCT
I am having trouble replacing exact cell references with named ranges in my formulas. Why does the following (with exact cell references) work: =SUMPRODUCT((DOCs!P408:P701="TF")*(DOCs!O408:O701="Y")) Answer: 1 (which is correct) But the following (substituting ranges for the cell references), doesn’t: =SUMPRODUCT((W_Type="TF")*(W_New="Y")) Generates Answer: 10 (wrong) What am I doing wrong? I'm running this from one sheet while the ranges are in another – both sheets are in the same workbook. The ranges are not entire columns & they are...

using COUNTA on a field that has been filtered
HELP What is the function to count entries on a column that has been filtered. COUNTA will still count the hidden fields, rather than just the shown fields Take a look at the SUBTOTAL function in help. =SUBTOTAL(3,"your range")) John "deirdre.campion@xtra.co.nz" <anonymous@discussions.microsoft.com> wrote in message news:036a01c53fd2$e1b1b8d0$a401280a@phx.gbl... > HELP > What is the function to count entries on a column that > has been filtered. COUNTA will still count the hidden > fields, rather than just the shown fields ...

WNet... functions in windows 98?
My program that uses WNet... functions (WNetOpenEnum, WNetEnumResource...) works only under windows 2000 and windows XP. Is there a way to use these functions with win 98 and if not, than how else can I enumerate network resources? Tnx in advance. Dragan ...

Credit Card Payments
I'm currently running Money 2005. The problem I'm having is with credit card payments. In my regular bank account anytime I make a CC payment I use "BILLS: Credit Card Payment" as the catagory. This works just fine... However, now that I can download my credit card statement with 2005 it's showing those payments as "income". So, when I run monthly reports it's not showing my true income for the month, it's showing my payroll checks plus all the credit card payments. Anyone has an idea of what category I can use in the CC account for those transac...

days used in a formula
does someone know how to insert the day of the month in a formula ? example =SUM(E26:E99)/here should be today's date Thanks for any help John- today() returns todays date day(today()) returns the day of the month weekday(today()) returns the weekday of the date Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107 View this thread: http://www.excelforum.com/showthread.php?threadid=556350 =SUM(E26:E99)/DAY(TODAY()) -- HTH Bob Phillips (replac...

Lookup
Hi I have a field in contact form called Id. I am looking up the contact on the campaign response form and want to populate the Id of the contact selected in a field on the campaign response form called CId, when the particular contact is looked up. Any ideas how i could go about this please? Any help would be appreciated - thanks heaps in advance Regards Ridhima You will need to fetch the contact id "on change" of the contact lookup field in your campaign response form using javascript. Then use this contactid value to fetch the id field for the contact (using ajax to call...

Personalizing the suffix quote and order sould be very useful.
It should very useful to be able to personalize the suffix quote and order in the autonumbering format, by exemple with the initials of the person who created the quote. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Communi...

Use word to associate an item in validation drop down list
I have created a validation drop down list but because the list can be quite long I want to be able to type a specific word on the cell that would associate that specific item in the drop down list. For example, lets say my drop down list contains the months of the year from January to December. I want to associate 1 = January and 2 = February and so on so that when I enter 6, June comes up on the cell. Does anyone know if this is possible on Excel? Could be done with VBA event code but your list would have to consist of numbers 1 to 12. Type a number and the appropriate m...

creation of mailing list in Publisher using already created Excel file
Iam trying to create a mailing list in Publisher and it wants me to manually enter addresses. I have an Excel file with all addresses in it. I cant find a way to import the file to Publisher. Can anyone help? Thanks Brian Help > Mail Merge. Would be good place to start. -- John G <brianmcgaha@gmail.com> wrote in message news:1165281212.680383.15900@n67g2000cwd.googlegroups.com... > Iam trying to create a mailing list in Publisher and it wants me to > manually enter addresses. I have an Excel file with all addresses in > it. I cant find a way to import the file to Publishe...

Excel Today() function
I need to create a formula that looks at a reference cell then returns Today() if the cell is between given values. If the the cell is not between given values then the date in the output cell doesn't change, is this possible??? Many thanks, Joel Joel, You can't do what you want with a formula. If you put a formula in a cell, that previous value is gone. -- HTH RP (remove nothere from the email address if mailing direct) "joel" <joel@discussions.microsoft.com> wrote in message news:868D02FC-9643-489D-9C6E-C5A108D328E9@microsoft.com... > I need to create ...

how to replace funtion call in MFC app with my function code:- tzset() ->my_tzset()
hi, I wanted to change tzset() & isindst() functionality, for this i modified the vc src file crt/src/tzset.c and linked with my application, chnages were made with cansole application without mfc, but with mdi mfc application, orignal code was used for localtime & other time function instead of modified tzfuntions. Then I decided to replace original function by traping the function call & calling modified function insted of orignal one but no success. I would like to know which technique is used when someone modifies the orignal vc code. Regards. dilbir ...

What to use, Serialize or OnFileSave and OnFileLoad?
I'm kind of confused as to the purpose of the Serialize function in the CDocument-derived class. Typically it has two blocks, if (ar.IsStoring()) and (ar.IsLoading()), and in each of these, you have pretty much exactly the same code as you would have in OnFileSave and OnFileLoad, respectively. Soooo ... is there a point to using Serialize and the OnFile<....> functions, or is it just redundant? Thanks! Cyde Weys wrote: > I'm kind of confused as to the purpose of the Serialize function in the > CDocument-derived class. Typically it has two blocks, if > (ar.IsStoring...