IF AND OR THEN ???

[LisaMarie1981]

Hi there!

I have to create a formula in cell B13 based on several criteria and I can't get past this first part. 

Start of formula is this:

=IF(AND(B11>=B2),(B11<C2),B12="Good")*(B11*(100%+B3))

But I need to change the end formula if it says Average or Bad to pull from the corresponding cells in the attached spreadsheet. 

But I also need to include multiple ranges, so if B11>=C2 and <D2, and then do the same for good, average and bad...and so on and so on.

Any help would be greatly appreciated!!! 

[#2 · Accepted solution]

@LisaMarie1981 

=($B$11*(100%+INDEX($B$3:$E$5,MATCH($B$12,$A$3:$A$5,0),MATCH($B$11,$B$2:$E$2,1))))

Does this return your expected result? 

[#3]

@LisaMarie1981 

What version of Excel do you have? If it's the most up-to-date (which is recommended) there are some new functions that could enable a more efficient formula.

For the time being, to show you what's possible -- albeit getting long and therefore harder to decipher -- I've attached a formula that uses the IFS function. So far, because I'm awaiting your answer with regard to the version of Excel you have, I've just dealt with the first column of values  (i.e., column B).

=IFS(

AND(B11>=B2,B11<C2,B12="Good"),B11*(1+B3),

AND(B11>=B2,B11<C2,B12="Average"),B11,

AND(B11>=B2,B11<C2,B12="Bad"),B11*(1+B5)

)

And as I write that, I see what may be a better way even with an older version of Excel. That would be via a lookup or match. But let me send you this for now. Let me (and anyone else looking in) what version of Excel you have.

[#4]

@LisaMarie1981 

One of the "fun things" about Excel is that there are always multiple ways to get from point A to point B.

@OliverScheurich has shown a way that dealt with the whole array of numbers and did so with a set functions that have been around for many years (INDEX and MATCH)

I've incorporated those same functions in a LET formula that might make for easier reading, but LET requires the 2021 edition or newer of Excel.

=LET(

tcol,MATCH(B11,B2:E2,1),

trow,MATCH(B12,A3:A5,0),

B11*(1+(INDEX(B3:E5,trow,tcol)))

)

The LET function enables you to define values, and then use them in the final calculation. In this case,

tcol is defined as the number of the column with cells B2:E2 that most appropriately matches the value in cell B11

trow is the number of the row that matches the quality word (good, avg, bad)

and then the formula multiplies B11 by 1 + the value in the "tcol-th" column and "trow-th" row of the range B3:E5 

[#5]

I have version 2210

[#6]

@LisaMarie1981 

In that case, the method you got from @OliverScheurich is your best bet.

[#7]

@OliverScheurich @mathetes
You guys are amazing!! we are so close. But...When I try to change the amount in B11 to 500,000 and B12 to Bad it should be 480,000 but it's coming up as 485,000, and if I change it to Good it should be 510,000 not 507,500, and I need the last part to be anything above E2 which doesn't seem to pull (ie. if I change B11 to 800,000 it doesn't pull from column E)

[#8]

@LisaMarie1981 

But...When I try to change the amount in B11 to 500,000 and B12 to Bad it should be 480,000 but it's coming up as 485,000, and if I change it to Good it should be 510,000 not 507,50

This is a matter of where the line is drawn. And that becomes, quite literally, a matter of pennies.

For example, if you change D2 to 501,000 then you get the result you're looking for for $500,000. For that matter, you can change D2 to 500,000.01 and get the results you're looking for. I suggest you add one penny to each of those top line numbers.  By the way, this does not work for B11 entries below 100K. I have been assuming that's your lowest boundary, but if that's not the case, you can just lower that number in B2, unless you'd want different factors for ranges below 100K. 

I need the last part to be anything above E2 which doesn't seem to pull (ie. if I change B11 to 800,000 it doesn't pull from column E)

Not sure what you're talking about. Using the spreadsheet @OliverScheurich created, and entering 800,000 I get the following.

The other thing for you to do here is to play around with this so that you understand how it's working and can maintain it yourself. You should feel free to change those numbers in row 2, for example, to see how that affects things. You can also change the percentages in rows 3-5.....one of the nice things about the formulas you've gotten is that the values are NOT hard-coded into the formulas (as they were with the IF and IFS formulas).......so you don't need to change the formula to update the calculations.

[#9]

@mathetes thank you so much for all your help and direction. You have been a tremendous help!! I can't tell you how much time this is going to save my company! Much appreciated!