Excel IF Formulas

[JeffITman]

I have a spreadsheet that will use three cells to calculate what i need.

It will basically be IF A1="value" and B1="value" and c1="value" then show price in D1.

I know how to use IF when only using one cell to answer yes or no.

I was thinking something like

=IF (a1="value" & b1="value" & c1="value", 15)

But that doesn't work.

Any suggestions?

 

[xlhelp]

=IF((A1=Value)*(B1=Value)*(C1=Value),15,"or something else")

Member- AAAA Association Against Acronym Abusers

 

[dallen43]

You could also use the AND function:

=IF (and(a1="value",b1="value",c1="value"), 15)

 

[JeffITman]

Is there any way to create more than 8 functions in one cell? I keep getting an error. I need about 40 functions in one cell...

 

[AnotherHiggins]

40 - whoa.

You can get around the limit of nested functions, but with 40 conditions there is almost certainly a better way to go.

Please provide some sample data and explain a few of the 40 criteria you'll be looking at.

Someone here should be able to point you to a more efficient (and more easily maintained) solution.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JeffITman]

I actually need 60...

this is what the first 8 look like

=IF((D7="value")*(E7>679)*(F7>350001),1000,IF((D7="value")*(E7>619)*(F7>350001),1125,IF((D7="value")*(E7>499)*(F7>350001),1150, IF((D7="value")*(E7>679)*(F7>224999),950,IF((D7="value")*(E7>619)*(F7>224999),1100,IF((D7="value")*(E7>499)*(F7>224999),1125, IF((D7="value")*(E7>679)*(F7>174999),825,IF((D7="value")*(E7>619)*(F7>174999),950,))))))))

Any suggestions would be great.

 

[mintjulep]

Generally, if there is no algorithmic solution to whatever you need to accomplish, a user defined function utilizing "select...case" would be the way to go.

I'm not willing to wade through your example to see if there is some sort of pattern that could be used for a more elegant solution.

 

[xlhelp]

Jeff the IT man,

You need to state your problem a lot clearer than that. Your formula does not take into account the fact that if E7 is greater than 679 then it is always greater than 499, 619 etc. I stopped there (because my head started to spin), but same would apply to F7. Unless it is only for George W's eyes only, can you state what you are trying to do?

Member- AAAA Association Against Acronym Abusers

 

[JeffITman]

We are a mortgage company with multiple branches. For each of those branches, we are trying to make a spread sheet that will allow us to figure out the amount owed to the corporate office for leads. I have a sheet of paper that has 60 difference scenarios (loan size, credit score, loan type). What I am trying to create is a spread sheet that I can enter those 3 variables in to and it look at the 60 scenarios and tell me what that particular lead costs.

Example:
Refinance
Loan Amount Credit Score <620 Credit Score 620-679 Credit Score >=680
$0 - 24,999 $185 $160 $150
$25,000 - 49,999 $290 $260 $225
$50,000 - 74,999 $445 $395 $375
$75,000 - 99,999 $525 $475 $450
$100,000 and above $575 $575 $550

So there's one Loan Type. I have 3 more that I have to use in this sheet. I would like the spread sheet to look at the Loan Type, then Loan Amount, Then credit score (or any of the 3 first) and return to me the cost...

 

[herkiefan]

Jeff,

If the RANGES of the loan amount and credit scores determine the lead cost as opposed to the ACTUAL amounts then you could set up and entry sheet that only calls for the various ranges using drop-down lists. The ranges along with the loan type could be merged to create a unique identifyer for each of the 60 scenarios. Using a simple vlookup table you could then look up the values for each scenario.

It shouldn't take too long to construct a table to do so. I built one using the criteia and information you supplied that could be updated if the ranges / costs change.

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[JeffITman]

Mike - Thanks for the response. Unfortunately, I'm not familiar with VLookup. Could you possibly help me out with that?

 

[mintjulep]

=INDEX(INDIRECT(B1),MIN(CEILING((B2+1)/25000,1),5),IF(B3>=680,3,IF(B3>=620,2,1)))

Where in B1 you enter the loan type
B2 enter the loan amount
B3 enter the credit score

Arange your costs into three 5 row x 3 column tables, name each table as the loan type.

 

[mintjulep]

If you feel the need to enter loan amounts to the penny then:

=INDEX(INDIRECT(B1),MIN(CEILING((INT(B2)+1)/25000,1),5),IF(B3>=680,3,IF(B3>=620,2,1)))

 

[herkiefan]

Jeff,

IMHO, the INDEX function would allow the users to enter in actual amounts instead of ranges. This method is much cleaner than my quick-n-dirty approach.

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[JeffITman]

How do i create the table so that the index formula knows where to find it?

Also, do I create the table by pressing data, table?

 

[mintjulep]

Just like your example above. You don't really need the column and row labels, although they don't hurt.

Use Insert | Name |Define to give a range name to each 5x3 group of costs. In your example the name might be "refinance".