IF Statement with multiple conditions

[IronMan1982]

Hi All

i need some help writing a formula with multiple conditions that returns a score depending on the %
-
in addition to this I also need another formula that would place a score in an adjacent cell depending on if the cell preceeding it is Green or Red (-10 for green, +10 for Red)

	A</SPAN>​	B</SPAN>​	C</SPAN>​	D</SPAN>​	E</SPAN>​	F</SPAN>​	G</SPAN>​	H</SPAN>​	I</SPAN>​	J</SPAN>​	K</SPAN>​
1</SPAN>​	Top 15's</SPAN>​		ALL</SPAN>​	LB</SPAN>​	NR</SPAN>​	TB</SPAN>​	VS</SPAN>​		%</SPAN>​	Risk Score</SPAN>​
2</SPAN>​									100-75</SPAN>​	5</SPAN>​
3</SPAN>​	Channel - Top 25</SPAN>​								74-60</SPAN>​	4</SPAN>​
4</SPAN>​									59 - 45</SPAN>​	3</SPAN>​
5</SPAN>​									44 - 30</SPAN>​	2</SPAN>​
6</SPAN>​									29 - 15</SPAN>​	1</SPAN>​
7</SPAN>​									14 - 0</SPAN>​	0</SPAN>​
8</SPAN>​
9</SPAN>​	DEBT CONSOLIDATION</SPAN>​
10</SPAN>​	*** Broker IQM Area***</SPAN>​	IQM Area</SPAN>​	ALL</SPAN>​	LB</SPAN>​	NR</SPAN>​	TB</SPAN>​	VS</SPAN>​	#N/A</SPAN>​	Grand Total 2015</SPAN>​	% of Debt Consolidation</SPAN>​	Risk Score</SPAN>​
11</SPAN>​				7</SPAN>​			10</SPAN>​	70.00%</SPAN>​
12</SPAN>​				6</SPAN>​			10</SPAN>​	60.00%</SPAN>​

<TBODY>
</TBODY>

 

[Mackers]

Hi

A few things to get you started:

If you change your lookup table in I and J to just include the minimum threshold and reverse the order so it goes from lowest to highest, you can use an inexact VLOOKUP. In this case the I column would read 0, 15, 30, 45, 60, 75 (or possibly these numbers divided by 100, depending on your data). You would then =VLOOKUP(your_%_value,I$2:J$7,2,1). This inexact match will return the J column corresponding to the nearest lowest value of the I column, so if your_%_value was, say, 38%, it would return the 30% risk score of 2.

You'll have to be more specific about matching on multiple conditions, I don't know what it is you want to return based on what conditions.

There aren't formulaic ways to change a cell based on formatting, and I would generally not recommend using colours to drive data rather than the other way round - however, if you formatted the cell to Green based on a formula, then you could reuse the original formula to add or subtract 10 (i.e. the formula is the common cause, rather than the formula driving the colour driving the addition/subtraction).

Hope that helps

Mackers

 

[taylor1010]

Hi
I am trying to create an if statement for multiple values (18) and I tried using the following but the limit is 7 - =IF(SEARCH("SOUTHPOINT",A:A),"SOUTHPOINT",),IF(SEARCH("DUBLIN",A:A),"DUBLIN",), and so on with 16 more values but it wont let me do more than 7, how does one extend the if formula to the amount I need?

 

[IronMan1982]

Hi

A few things to get you started:

If you change your lookup table in I and J to just include the minimum threshold and reverse the order so it goes from lowest to highest, you can use an inexact VLOOKUP. In this case the I column would read 0, 15, 30, 45, 60, 75 (or possibly these numbers divided by 100, depending on your data). You would then =VLOOKUP(your_%_value,I$2:J$7,2,1). This inexact match will return the J column corresponding to the nearest lowest value of the I column, so if your_%_value was, say, 38%, it would return the 30% risk score of 2.

You'll have to be more specific about matching on multiple conditions, I don't know what it is you want to return based on what conditions.

There aren't formulaic ways to change a cell based on formatting, and I would generally not recommend using colours to drive data rather than the other way round - however, if you formatted the cell to Green based on a formula, then you could reuse the original formula to add or subtract 10 (i.e. the formula is the common cause, rather than the formula driving the colour driving the addition/subtraction).

Hope that helps

Mackers

Thank you Mackers I will give that a go. Also thanks for advice about colour situation. I need to learn how to use the index function I think.

 

[Mackers]

Index is a fairly straightforward function for basic use but a very powerful tool for advanced use.

The most basic useful index function is:

=INDEX(YourDesiredSingleColumnOrRow, MATCH(YourValue, CorrespondingValues, 0))

This is essentially a slightly more powerful version of a VLOOKUP - you are comparing one value to a row or column (say trying to find a name in a list of names), finding the position of the matching value (say the 4th row down), then returning the corresponding value of a different row or column (say finding the salary corresponding to the person in the 4th position).

If you have any questions or specific implementation requirements let me know, I'd be glad to help.

Mackers

 

[IronMan1982]

thanks mate i will try and send you the data and explanation of what i want too do and see if it is a better option than vlookup.

 

[Mackers]

Hi
I am trying to create an if statement for multiple values (18) and I tried using the following but the limit is 7 - =IF(SEARCH("SOUTHPOINT",A:A),"SOUTHPOINT",),IF(SEARCH("DUBLIN",A:A),"DUBLIN",), and so on with 16 more values but it wont let me do more than 7, how does one extend the if formula to the amount I need?

Hi taylor

As an array function, executed using control+shift+enter:

=INDEX(A:A,MIN(IFERROR(MATCH({"SOUTHPOINT","DUBLIN","CHICAGO","CORK"},A:A,0),"")))

Add in any cities in the curly bracers {"City1","City2","City3",...,"Cityn"}. Note that if your column A:A has more than one matching value from the list of cities, say both Southpoint and Dublin, then it will return the first one in the MATCH() list. Also in general it's a good idea to start a new topic if you have a question.

Hope that helps

Mackers