Excel comparison with multiple IFs

问题:

I have a scale, based on which I decide the value of the coefficient for the multiplication. The scale looks as following:

Which means that:

• for Category1: when value>=1.000.000 then coef is 1, when value>=500.000 then coef is 0.8 and etc.

• Same logic applies for Category2;

Then I have input data in the following format:

Company !MainCat|Sales Amount|
Company1|T1     |   6.500.000|
Company2|T2     |      70.000|

I need to find corresponding coefficient, ratio of the coeffitient and the value (=ratio*MaxCoef). Currently, I am finding coef the following way: - for company1:

 =IF(C8>=$D2;$D$1;IF(C8>=$E2;$E$1;IF(C8>=$F2;$F$1;IF(C8>=$G2;$G$1;IF(C8>=$H2;$H$1;IF(C8>=$I2;$I$1))))))

That is literally hardcoded and doesn't look good. Maybe there is a better way of doing ? Any suggestions?

Formula view:

回答1:

You can COUNTIF(range, [criteria] < value) * 0.2 as your add 0.2 per coef stage.

To you data do: =COUNTIF(D2:H2, "<"&C8) * 0.2, count how many stages the value passes * the value per stage.

Your count if range needs to be until H2 as I2 is 0, so inferior to value and gets counted.

To combine the COUNTIF() with a dynamic search for the right category based on MainCat you can MATCH() the MainCat with Code which will give the row where the Code is located and utilize INDIRECT() to apply it as range.

=COUNTIF(INDIRECT("D"&MATCH(B8,B:B,0)&":H"&MATCH(B8,B:B,0)),"<"&C8)*0.2

MATCH(B8,B:B,0) - will match the value on B8 (lets say T1) and return the row 2.

INDIRECT("D"&MATCH(B8,B:B,0)&":H"&MATCH(B8,B:B,0) = INDIRECT("D"&2&":H"&2) - will turn the text into an actual range to be use by the COUNTIF().

回答2:

Create a table ‚Mapping’ that contains two columns, ‚Category’ and ‚Coefficient‘, then use INDEX-MATCH on it as described in https://www.deskbright.com/excel/using-index-match/.

=INDEX(Mapping[Category]; MATCH([Coefficient]; Mapping[Coefficient]; -1))

This example assumes that you put this formula into a table that has a column named ‚Coefficient‘ with the input value to your multiple IFs. The trick is that as a match_type argument, provide either -1 or 1, according to your needs.

回答3:

You can do this in VBA. Write your own function which ends in something like that

=MyOwnScale(C8; B8; A2:I3)

The first parameter of your VBA-function is the value, the second the category and the third is the range with the thresholds. So you can move your cascading IF-loops in VBA-Code and you (and your users) see only a clean function call in the cell.