Link to home
Start Free TrialLog in
Avatar of ryan80
ryan80

asked on

help with Excel formula - simplfy nested if statements

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

Avatar of KnutsonBM
KnutsonBM
Flag of United States of America image

could you post your sheet, that could make this a lot easier......

thanks,

Brandon
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of ryan80
ryan80

ASKER

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
Would VBA be an option for you ?
gowflow
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))
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)
Your file with Barry's formula
server-inventory-core-count-temp.xlsx
Avatar of ryan80

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ryan80,
Do remember that it is Barry's formula. I'm just 'splaining how it works.

Brad
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.  
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
Avatar of ryan80

ASKER

Thanks,

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