Solved

help with Excel formula - simplfy nested if statements

Posted on 2011-03-22
14
401 Views
Last Modified: 2012-05-11
i have an excel formula in Excel 2007 that works, but is very ugly. I am sure that there is a better way to do this. What I am trying to do is produce a count of the amount of cores in my servers. I have the count of processors and their corresponding names, a list of the processors with their corresponding core count.

I need to do a search of the processor name which looks something like this:

Intel Xeon E5640 2.67GHz


And then reference it against the table that has the processor name and core count, which looks like this

5640                                       6

So basically I want it to search for the processor name (ie, 5640, 5660, 5670......) and return the corresponding value from another table. Right now I am using a very long nested if statement, but it will get longer and longer as more processor models are added and there has got to be a better way. Here is the if statement:

=IF(ISNUMBER(SEARCH("3430",H17)),4,(IF(ISNUMBER(SEARCH("5120",H17)),2,(IF(ISNUMBER(SEARCH("5160",H17)),2,(IF(ISNUMBER(SEARCH("5506",H17)),4,(IF(ISNUMBER(SEARCH("5560",H17)),4,(IF(ISNUMBER(SEARCH("5620",H17)),4,(IF(ISNUMBER(SEARCH("5640",H17)),6,(IF(ISNUMBER(SEARCH("5650",H17)),6,(IF(ISNUMBER(SEARCH("5670",H17)),6,(IF(ISNUMBER(SEARCH("7420",H17)),4,(IF(ISNUMBER(SEARCH("7460",H17)),6,1)))))))))))))

0
Comment
Question by:ryan80
14 Comments
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 35191899
could you post your sheet, that could make this a lot easier......

thanks,

Brandon
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 300 total points
ID: 35192093
If your table is in Y2:Z20 then try this formula

=LOOKUP(2^15,SEARCH(Y$2:Y$20,H17),Z$2:Z$20)

regards, barry
0
 
LVL 80

Expert Comment

by:byundt
ID: 35192217
Note that Barry's elegant formula requires that every cell in Y2:Y20 contain a value. If one or more is blank, the formula will erroneously return a value from column Z on the same row as the last blank cell.

Brad
0
 
LVL 12

Author Comment

by:ryan80
ID: 35192233
here is the excel file.

the whole picture is that Server 2003 reports the core count. 2008 reports the processor count. So i need to go through and when the operating system is 2008, multiply the proccesor count times the amount of cores for that model, which a list of is contained in the second sheet.

Hope that helps

@barryhoudini

the problem that I have, is that the value range and the result range are the ones that are the same. The lookup_range is not the same as the result_range. It needs to look for a value, and return a specific number if it finds that value.
server-inventory-core-count-temp.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35192255
Would VBA be an option for you ?
gowflow
0
 
LVL 80

Expert Comment

by:byundt
ID: 35192366
Try the following modification of Barry's formula in cell K7, then copy it down:
=IF(ISNA(LOOKUP(2^15,SEARCH(Sheet2!A$1:A$11,E7),Sheet2!B$1:B$11)),1,LOOKUP(2^15,SEARCH(Sheet2!A$1:A$11,E7),Sheet2!B$1:B$11))
0
 
LVL 80

Expert Comment

by:byundt
ID: 35192412
I changed your lookup table by adding a blank cell in A1 and the number 1 in B1. You can now use Barry's original formula:
=LOOKUP(2^15,SEARCH(Sheet2!A$1:A$12,E7),Sheet2!B$1:B$12)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
ID: 35192422
Your file with Barry's formula
server-inventory-core-count-temp.xlsx
0
 
LVL 12

Author Comment

by:ryan80
ID: 35193021
thanks, that looks a lot nicer than what I had. Could you help me understand a few things though?

The SEARCH function, what does it return? When I copy that formula out to another cell, it does not work. From what it looks like, it will return the cell that contains the value that is found? do the $ before the cell #'s signify this?

Also why is 2^15 used in the LOOKUP function?
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 35193301
SEARCH returns an array of #VALUE! error values and string starting positions. In this case, the first result in the array (searching for nothing) returns the starting position of 1. And the exact match for the four digit number returns a larger starting position. We need to search this array and return a value from the same row in column Z.

The reason that SEARCH isn't working in a separate cell is that it returns an array of values, one for each row in the lookup table.

If it cannot find an exact match and there is nothing bigger in its second parameter, the LOOKUP function has the useful property of returning the last value that it found of the same data type. The number 2^15 is bigger than any possible match, so you get a value from column Z in the same row as the last matching value from column Y.

The $ before the row numbers just mean that they should remain fixed when you copy the formula down. Row numbers without the $ automatically increase by 1 with each successive row in the copy--so the formula is always searching column E in the same row as the formula.

You may find it helpful to use the Formulas...Evaluate Formula menu item. Click the Evaluate button several times to watch how Excel interprets the formula.
0
 
LVL 80

Expert Comment

by:byundt
ID: 35193310
ryan80,
Do remember that it is Barry's formula. I'm just 'splaining how it works.

Brad
0
 

Expert Comment

by:ussc1
ID: 35202242
Is this solution meeting the need?  I would use INDEX so the table doesn't need to be sorted and so it will skip blanks.  
0
 
LVL 80

Expert Comment

by:byundt
ID: 35202297
ussc1,
The table doesn't need to be sorted. LOOKUP is trying to find a value that is bigger than any possible return from SEARCH, so sorting is no longer an issue.

The only requirement is that the blank cell be the first row in the table, and that there be no more blank cells.

Brad
0
 
LVL 12

Author Closing Comment

by:ryan80
ID: 35206684
Thanks,

The formula works great, and that explanation really helps me understand what is going on.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now