How to convert Excel values into buckets based on two criteria-CodePudding

I'm trying to put data into different groups that take into account the value of two columns and have that value populate in the last cell of the row.

Column C has a value of Complete, Incomplete or Overdue Column I has a numeric value

I want the formula to look at C and then pick a grouping based on the number in I

For example, if C=Complete and I=4 I want it to fall into the "Completed 1-15 Days Late" Group. Similarly if C=Overdue and I=35 it should fall into "30 Days Overdue."

I have IF statements for the groupings based on Column I, but I can't figure out how take into account Column C.

Example IF:

=IF(I2>30,"Completed 30  Days Late",if(I2>15,"Completed 15-30 Days Late",if(I2>0,"Completed 1-15 Days Late",if(I2=0,"Completed On Time",""))))

CodePudding user response：

You can use Match and Choose to make the formula a little easier to figure out then a whole bunch of nested IF statements, though it's still quite long:

 =IF(C4="Complete",CHOOSE(MATCH(I4,{0,1,15,30}),"Completed On Time","Completed 1-15 Days Late","Completed 15-30 Days Late","Completed 30  Days Late"),IF(C4="Overdue",CHOOSE(MATCH(I4,{0,1,15,30}),"","1-15 Days Overdue","15-30 Days Overdue","30  Days Overdue"),""))