Excel Formula

Hey guys. I would greatly appreciate anyone that can help me out.
Not sure which formula is needed. and/if or V looking


1) I am looking for a formula that will determine the Initial Rank.
Initial Rank will be determined by the following
-Type of Data
-Tier Level
-Spend

I created a table in the spreadsheet that list all the possible
25 combinations and the appropriate rank.


2) I am also looking for a formula that will determine the Final Rank.
Final Rank will be determined by the following
-Number of Customers
-Frequency
-*Type of data*

I created a table in the spreadsheet that list all the possible
combinations and the appropriate rank.

***Please note: If the "Number of Customers" is "Less than 10,000 and the "Frequency" is "Daily" or "Weekly" and the Type of Data is" Privacy & Secure Data" than the Final Rank
will be High. ***

In all other instances the Type of data will not matter.

Please see attachment. I have uploaded the spreadsheet.

combinations is in the source tab

Thank you guys.
Copy-of-Book1.xlsx
sammenaceAsked:
Who is Participating?
 
Ken ButtersCommented:
Yes... the two lookups are somewhat unrelated....

the first lookup for initial rank builds a key from columns B & C ... so you take the two strings in columns B&C... and find a matching string in the first table in the "Key" column.  

In your latest spreadsheet example... every instance of B & C exists in the first table... some examples:

PHI1
PHI & SPI5

so that is why a match was found in every row for Initial Rank.

For Final Rank:

2nd lookup for final rank works in a similar fashion... but it builds a different key.. and that key derived by concatenating values from E2 + F2 + B2 and so on ....
first key built is : "More than 10,000DailyPhi"

If you look at the 2nd table... you do not have an entry for  "More than 10,000DailyPhi".. .so the final rank is blank... meaning no match found.

If you had a row in the 2nd table where
o Number of Records is "More than 10,000"
o Freqency is "Daily"
o Type of Data is "PHI"

then you would have a match for the first row on sheet 1... and it would populate the final rank from that 2nd table on source tab.
0
 
nutschCommented:
Initial rank, entered with Ctrl+Shift+Enter as an array formula:

=INDEX(Source!$J$2:$J$26,MATCH(B2&C2&D2,Source!$G$2:$G$26&Source!$H$2:$H$26&Source!$I$2:$I$26,0))

Final rank, also an array formula:

=IF(AND(B2="Privacy & Secure Data",F2="Less than 10,000",OR(G2="Weekly",G2="Daily")), "High",INDEX(Source!$O$3:$O$26,MATCH(F2&G2&"NA",Source!$L$3:$L$26&Source!M3:M26&Source!$N$3:$N$26,0)))

Thomas
0
 
sammenaceAuthor Commented:
Thomas-

Not familiar with array formulas.

How do i copy the Initial Rank and Final Rank formulas
into Column E (initial Rank) and Column H (Final Rank)

I keep getting a #VALUE! error
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Ken ButtersCommented:
I added a some columns on source tab to create a lookup key.. then used vlookup.

see attached

Note: also converted the two tables to tables.  This allows auto update of range if you add to table by putting cursor in last cell of table and hitting tab.
Copy-of-Copy-of-Book1.xlsx
0
 
Ken ButtersCommented:
to update array formulas.... first copy nutcsh formula to clipboard...

for example:
=INDEX(Source!$J$2:$J$26,MATCH(B2&C2&D2,Source!$G$2:$G$26&Source!$H$2:$H$26&Source!$I$2:$I$26,0))

then select cell e2

Paste in formula

then while holding down the Ctrl key and the shift key... hit enter

Then just fill down.

Repeat for cell H2 and other formula.
0
 
sammenaceAuthor Commented:
Let me test this out and i'll let you guys know

thank you so much guys!
0
 
sammenaceAuthor Commented:
Hey guys thank you for both of your help. Forumla seems to be ok.
I did notice one error

In column F if the Number of Records is "Less than 10,000" and the Freuency is "Daily" or "Weekly" the Final Rank will be "High" if the Type of Data" is PHI, SPHI or PHI & SPI from column B. (This is what i wanted)

My question is if the Type of Data is "BCI" or "No Sensitive Data"  in column B and the Number of Records is "Less than 10,000 in column F and the frequency is "Daily" or "Weekly" I need the Final Rank to show up as "Medium" and not "High" as it currently does.

In other words:
If the "Type of data" is PHI, SPI or PHI & SPI and the Number of records is "Less than 10,000 and the frequency is Daily or Weekly the Final Rank should be HIGH

However; if the "Type of data" is BCI or No Sensitive Data and the Number of records is "Less than 10,000 and the frequency is Daily or Weekly the Final Rank should be Medium

Is there a formula that ties column B with column F & G to get the final results in column H ?
Testing101.xlsx
0
 
Ken ButtersCommented:
in the second table... the type of data set to NA... i am assuming that means that the type of data is not used to determine final rank.

I changed the formula in column 1 of that table... where I create the lookup key... to NOT make it part of the lookup key if the value is NA.

otherwise it will be part of the lookup key.

I think that change ... in combination with building the same key on the source page should do it...

However... you will need to add additinal rows in the second table to account for the fact that you want those values also for PHI.... and SPI.  There are currently no rows with data that matches that criteria in the second table... so the lookup is blank for those... with the copy I am attaching.
Copy-of-Testing101.xlsx
0
 
sammenaceAuthor Commented:
buttersk-

Thank you once again for your assistance.

1) I did make a user error. I decided that i do not need the "Spend" column.
I deleted the column. I tried to update the formula but i am getting #REF! message.
So the intial Rank will only be determied by Type of Data and Tier Level.

The formula i put in is:

=IF(ISNA(VLOOKUP(CONCATENATE(B2,C2,),InitalRankLookup,5,FALSE)),"",VLOOKUP(CONCATENATE(B2,C2,),InitalRankLookup,5,FALSE))

2) You are right the NA are values that i do not want to include in the Final rank.
I did update the table so all the possible combinations are now in there if the type of data is PHI, SPI and PHI & SPI. I added the last 6 rows in the table.

However after making the changes i dont know what the new formula would be.
When i choose Number of Record and Frequency the final rank is empty. What is the formula?

Thank you once again for your help.
Final-Test.xlsx
0
 
Ken ButtersCommented:
Yes... I see the problem.. it is in the VLOOKUP function:
=VLOOKUP(CONCATENATE(B2,C2,),InitalRankLookup,4,FALSE)

first parameter creates the key to lookup:    CONCATENATE(B2,C2,)
this takes whatever is in b2 and c2 and combines them to create a key

second parameter is where to look: InitalRankLookup
InitialRankLookup is a table... you can see how it is defined by going to formulas tab and looking at name manager

3rd parameter is the problem... it tells which column of the IntitialRankLookup is to be returned when a match is found.... we used to have 5 columns... and we wanted the last one... now we have only 4 columns... so we need to make the following change

FROM:
VLOOKUP(CONCATENATE(B2,C2,),InitalRankLookup,5,FALSE)
TO:
VLOOKUP(CONCATENATE(B2,C2,),InitalRankLookup,4,FALSE)

The vlookup is used twice in the same formual... once just to check to see if it returns #NA and the second time to return the actual value... so you will need to change the 5 to 4 in both instances ... then fill down.  

That will correct our #REF error.
0
 
sammenaceAuthor Commented:
The initial rank is working! however for the Final Rank isnt still getting an error

 i am using the following formula

=IF(ISNA(VLOOKUP(CONCATENATE(E2,F2,B2),FinalRankLookup,3,FALSE)),"",VLOOKUP(CONCATENATE(E2,F2,B2),FinalRankLookup,3,FALSE))


I updated the table so all the combinations are in there now.

Getting an  FALSE response
0
 
nutschCommented:
Have you tried with the array formulas at all?
0
 
sammenaceAuthor Commented:
Thomas,

Thanks for checking in.

I tried the array formula but i was told the VLOOKUP is better.
I am new to excel so i don't have much knowledge in excel.

I changed my tables as well. Took out the spend column in the initial rank.
Also in the Final Rank i needed to add the value from column B (type of data)
into the Number of Records and the Frequency to get the Final Rank

I didnt ask for this request when i first submitted my question.
0
 
Ken ButtersCommented:
Looks like a new column was added to 2nd table which I didnt notice.

In case you need to change it in the future... here is one way:

Highlight existing table (K3:N16 for example)

select design tab

click on resize table (see screenshot)

then specify the columns/rows to make sure all necessary data (including new column O) is included in definition of the table.

I've also updated / attached spreadsheet with that change.

Final rank formula for attached spreadsheet should be :

=IF(ISNA(VLOOKUP(CONCATENATE(E2,F2,B2),FinalRankLookup,5,FALSE)),"",VLOOKUP(CONCATENATE(E2,F2,B2),FinalRankLookup,5,FALSE))

Note: it is returning column 5 of the table...You will need to adjust formula if you are no longer using column 5 as value to return for final Rank.
screenshot-excel.jpg
Copy-of-Final-Test.xlsx
0
 
sammenaceAuthor Commented:
buttersk

I opened the excel spreadsheet and i notice even with the correct formula the Final Rank is blank. But the Final Rank appears if the Initial Rank is left empty and vice versa.

In row 2 - when type of data, tier level is filled the initial rank is generated. when i fill in the
number of record , frequency the final rank is blank

any idea why Final Rank is not generating?

please see attachment
Final-Test-1.xlsx
0
 
Ken ButtersCommented:
Also just noticed this... in 2nd table on source sheet PHI&SPI have no spaces.
on sheet1 ... under type of Data "PHI & SPI" have spaces...anything built using PHI&SPI would never match key for that reason... both of those need to match.
0
 
sammenaceAuthor Commented:
buttersk

THANK YOU!

I love how you explained the issue. Really easy to understand.
I greatly appreciate your help!
0
 
sammenaceAuthor Commented:
Really helpful. Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.