SOLVED

# IF AND OR THEN ???

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!!!

8 Replies
best response confirmed by mathetes (Respected Contributor)
Solution

# Re: IF AND OR THEN ???

``=(\$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?

# Re: IF AND OR THEN ???

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 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.

# Re: IF AND OR THEN ???

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

@Quadruple_Pawn 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

# Re: IF AND OR THEN ???

I have version 2210

# Re: IF AND OR THEN ???

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

# Re: IF AND OR THEN ???

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)

# Re: IF AND OR THEN ???

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 @Quadruple_Pawn 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.

# Re: IF AND OR THEN ???

@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!