Return a value based on the type (new or existing) and value (from a range of values).

[tricky321]

Range	of	Values		New	Existing
1	to	10		10%	8%
10	to	50		3%	2.5%
50	to	100		.8%	.4%

<tbody>
</tbody>

Type	Value		Percentage to Return (from the New/Existing columns above)
New	40		??? -What formula to use???
Existing	40		??? -What formula to use???
New	60		??? -What formula to use???
Existing	60		??? -What formula to use???

<tbody>
</tbody>

The first table above is the rules. In the second table under the column in red ("Percentage to Return"), I'm trying to figure what formula will return the appropriate % based on the "Type" being New or Existing and the Value falling into the specific ranges listed in the first table.

I've tried some embedded If statements, but have come up unsuccessful...

Any help will be much, much appreciated!

 

[schwiggiddy]

Re: Need to return a value based on the type (new or existing) and value (from a range of values).

Hi Tricky,

I think you're on the right track with the nested if statements. Here's what I came up with:

=IF(A7="New",IF(B7<=$B$2,$C$2,IF(B7<=$B$3,$C$3,IF(B7<=$B$4,$C$4,"Value too high."))),IF(B7<=$B$2,$D$2,IF(B7<=$B$3,$D$3,IF(B7<=$B$4,$D$4,"Value too high."))))

So I copied your info starting with headers in A1 and the second table starting with headers in A6. The exception is that instead of Range -- Of -- Value, I condensed it to two columns: min and max. "A
is the mins, "B" is the maxes and the if statements only look at the maxes to see if the value is less than or equal to that max.

In other words the formula looks like:

=if(type = "New", if(value <= max1, then new1, if(value <= max2, then new2, if(value <= max3, then new3, "too high"))),if( ... and repeat, using existing in place of new.

Hope that's clear enough to understand.

 

[tricky321]

Re: Need to return a value based on the type (new or existing) and value (from a range of values).

Thank you very much for the assist. schwiggiddy! I believe I was incorrectly coding the value_if_false from original IF statement that analyzed "type = 'New'" to the next one analyzing "type = 'Existing'". Good to go now!

The full formula I ended up with is (assuming approximately the same cell positions as mentioned by 'schwiggiddy', above):
=IF(A9="New",IF(B9<=$B$2,$C$2,IF(B9<=$B$3,$C$3,IF(B9<=$B$4,$C$4,"too high."))),IF(A9="Existing",IF(B9<=$B$2,$D$2,IF(B9<=$B$3,$D$3,IF(B9<=$B$4,$D$4,"too high.")))))

 

[schwiggiddy]

Re: Need to return a value based on the type (new or existing) and value (from a range of values).

Thank you very much for the assist. schwiggiddy! I believe I was incorrectly coding the value_if_false from original IF statement that analyzed "type = 'New'" to the next one analyzing "type = 'Existing'". Good to go now!

The full formula I ended up with is (assuming approximately the same cell positions as mentioned by 'schwiggiddy', above):
=IF(A9="New",IF(B9<=$B$2,$C$2,IF(B9<=$B$3,$C$3,IF(B9<=$B$4,$C$4,"too high."))),IF(A9="Existing",IF(B9<=$B$2,$D$2,IF(B9<=$B$3,$D$3,IF(B9<=$B$4,$D$4,"too high.")))))

No problem! Be aware you might want to add a error handler in case there's ever a time when the field doesn't say "New" or "Existing," maybe from a typo. Or maybe use Data Validation -> List and enter "New,Existing" into the source line.