Formula based on criteria..

Question

Formula based on criteria..

Chaturvedi, Santosh 240

Hello I have one conditions

I have values in column W & X,

They needed to be coompare these values with each other based on criteria as shown in boxes

Once it fulfilles the criteria the coumun y must say Feild, Feild II, Fiel II or Field IV see in red fonts.

Please hlep

User's image

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Gabriel-N 20,305 Microsoft External Staff Moderator

Hello @Chaturvedi, Santosh

If you're using Excel M365, please paste this formular to Y4 =IF(OR(ISBLANK(W4), ISBLANK(X4)), "", IFS(X4 < -W4, "Field I", AND(X4 >= -W4, X4 <= W4), "Field II", AND(X4 > W4, X4 <= 3*W4), "Field III", X4 > 3*W4, "Field IV"))The IFS function is the cleanest way to map the specific criteria boxes from the screenshot.

If you're using older versions of Excel, a standard nested IF statement evaluates the conditions in sequential order: =IF(OR(ISBLANK(W4), ISBLANK(X4)), "", IF(X4 < -W4, "Field I", IF(X4 <= W4, "Field II", IF(X4 <= 3*W4, "Field III", "Field IV"))))

User's image

Hope this helps.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Chaturvedi, Santosh 240 Reputation points

2026-06-25T15:00:38.46+00:00

Hello this is working perfect..

I have implemented your formula--This is working fine in cell H7 onwards..

A extended request --> When the

H7 -->Field I _-> E7 must calculatethe value by formula which is mentionn in black box

H23 -> Field III--> calcultate blue box

H24 -> Field III--> calcultate orange box

XYz --> Feild II -- Then calculate red box
Gabriel-N 20,305 Reputation points Microsoft External Staff Moderator

2026-06-25T15:14:44.5166667+00:00
Hello @Chaturvedi, Santosh

Please try this formular in E7 (Note: Cell F4 must be absolutely locked to $F$4 to avoid value errors when dragging the formula down to the rows below)

Excel M365: =IFS(H7="Field I", 1/(1-$F$4), H7="Field II", 1/(1+$F$4*(C7/B7)), H7="Field III", (3+$F$4)/((1+$F$4)*3+$F$4*(C7/B7)), H7="Field IV", (3+$F$4)/(3*(1+$F$4)^2), TRUE, "")

Older versions of Excel: =IF(H7="Field I", 1/(1-$F$4), IF(H7="Field II", 1/(1+$F$4*(C7/B7)), IF(H7="Field III", (3+$F$4)/((1+$F$4)*3+$F$4*(C7/B7)), IF(H7="Field IV", (3+$F$4)/(3*(1+$F$4)^2), ""))))
Gabriel-N 20,305 Reputation points Microsoft External Staff Moderator

2026-06-26T15:16:48.6466667+00:00

Hi @Chaturvedi, Santosh

If you still have further concerns, please feel free to let me know.
Chaturvedi, Santosh 240 Reputation points

2026-07-06T13:01:41.4933333+00:00

Hello Gabriel--> The above formulation is perfectly working.

Here is one situation that in one entry the values in the excel sheet do not fall in feild I, Feild II. Field III, Field IV (see image below)

Is it possible to modify the formula so that if any value do not fall in any four cases as shown above then ,

It opts to get the formula nfor Field II. ,,

=IFS(H717="-","-",

H717="F-I",1/(1-$G$70),

H717="F-II",1/(1+$G$70(C35/B35)),*

H717="F-III",(3+$G$70)/((1+$G$70)3+$G$70(C35/B35)),

H717="F-IV",(3+$G$70)/(3*(1+$G$70)^2),TRUE,"")

If a case does dot falls in any above case then it follows the "H717="F-II",1/(1+$G$70*(C35/B35)),"

Please mofify theformulation.

Image :
Gabriel-N 20,305 Reputation points Microsoft External Staff Moderator

2026-07-06T14:27:04.3033333+00:00

Hello @Chaturvedi, Santosh

Looking at the screenshot you shared, it appears that Excel is trying to divide C35 by B35, even though the formula is currently located on row 717.

Normally, when a formula is copied or filled down, the references should automatically adjust to the current row (for example, C717/B717). If the formula is still referencing C35/B35, it may indicate that the references have been locked with $ signs or that the formula was copied in a way that preserved the original row references.

Also, if B35 contains text, is blank, or contains a non-numeric value, Excel cannot perform the division and will return a #VALUE! error.

As a test, could you try the following formula instead?

=IFS(H717="-", "-", H717="F-I", 1/(1-$G$70), H717="F-II", 1/(1+$G$70*(C717/B717)), H717="F-III", (3+$G$70)/((1+$G$70)*3+$G$70*(C717/B717)), H717="F-IV", (3+$G$70)/(3*(1+$G$70)^2), TRUE, 1/(1+$G$70*(C717/B717)))
Chaturvedi, Santosh 240 Reputation points

2026-07-06T14:47:52.8666667+00:00

I appply this formula and it is working perfect -- Thanks for this suppport !!