Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

help with Excel formula - simplfy nested if statements

Posted on 2011-03-22
14
Medium Priority
?
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1200 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 81

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 31

Expert Comment

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

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 81

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

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 81

Assisted Solution

by:byundt
byundt earned 800 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 81

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 81

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

597 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