I'm looking for help developing a formula that will return the results...
1st, 2nd, 3rd, 4th, etc 51st, etc.
[TABLE="width: 1549"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Department[/TD]
[TD]TO[/TD]
[TD] Variance[/TD]
[TD]CC![/TD]
[TD]CC2[/TD]
[TD]CC3[/TD]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Bill[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (99.95)[/TD]
[TD][/TD]
[TD]6th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Sam[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (11.07)[/TD]
[TD][/TD]
[TD]15th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]April[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (9.76)[/TD]
[TD][/TD]
[TD]8th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Scott[/TD]
[TD]CHEF[/TD]
[TD]Outlet Manager[/TD]
[TD] 14.00[/TD]
[TD][/TD]
[TD]10th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Randy[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] (14.00)[/TD]
[TD][/TD]
[TD]3rd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Sarah[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] 17.00[/TD]
[TD][/TD]
[TD]2nd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Glenn
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (5.01)[/TD]
[TD][/TD]
[TD]26th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Larry
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] 106.00[/TD]
[TD][/TD]
[TD]28th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]87[/TD]
[/TR]
</tbody>[/TABLE]
Date, Name and Variance are input cells.
Department and TO are Vlookups. I just created a formula for CC3 which is: =IF(E2="","",VLOOKUP(ABS(E2),$P$2:$Q$8,2,TRUE)). It looks up the variance and determines which range to return.
I wanted to create a formula for CC2 which I assume would use a Hlookup (The Column headers -
[TABLE="width: 1549"]
<tbody>[TR]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[/TR]
</tbody>[/TABLE]
Then lookup the corresponding amount in the table. EG. The first example is between 50-199.99. The table states it to be 6 as the result. Then the formula would convert the 6 to 6th.
I found a couple formula's that I think might be able to be combined with an hlookup but I'm not sure.
Where A1 would be the number to be converted into a ranking.
=A1&
IF(AND(MOD(A1,10)<4,OR(A1<10,A1>13)),MID("thstndrd",1+MOD(A1,10)*2,2),"th")
Thank you for taking the time to read.
1st, 2nd, 3rd, 4th, etc 51st, etc.
[TABLE="width: 1549"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Department[/TD]
[TD]TO[/TD]
[TD] Variance[/TD]
[TD]CC![/TD]
[TD]CC2[/TD]
[TD]CC3[/TD]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[TD]YTD[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Bill[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (99.95)[/TD]
[TD][/TD]
[TD]6th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/27/15[/TD]
[TD]Sam[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (11.07)[/TD]
[TD][/TD]
[TD]15th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]April[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (9.76)[/TD]
[TD][/TD]
[TD]8th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Scott[/TD]
[TD]CHEF[/TD]
[TD]Outlet Manager[/TD]
[TD] 14.00[/TD]
[TD][/TD]
[TD]10th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Randy[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] (14.00)[/TD]
[TD][/TD]
[TD]3rd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Sarah[/TD]
[TD]SERVER[/TD]
[TD]Food Manager[/TD]
[TD] 17.00[/TD]
[TD][/TD]
[TD]2nd[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Glenn
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] (5.01)[/TD]
[TD][/TD]
[TD]26th[/TD]
[TD]$5.00-$24.99[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD]02/28/15[/TD]
[TD]Larry
[/TD]
[TD]BAR[/TD]
[TD]Bar Manager[/TD]
[TD] 106.00[/TD]
[TD][/TD]
[TD]28th[/TD]
[TD]$50.00-$199.99[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]87[/TD]
[/TR]
</tbody>[/TABLE]
Date, Name and Variance are input cells.
Department and TO are Vlookups. I just created a formula for CC3 which is: =IF(E2="","",VLOOKUP(ABS(E2),$P$2:$Q$8,2,TRUE)). It looks up the variance and determines which range to return.
I wanted to create a formula for CC2 which I assume would use a Hlookup (The Column headers -
[TABLE="width: 1549"]
<tbody>[TR]
[TD]5-24.99[/TD]
[TD]25-49.99[/TD]
[TD]50-199.99[/TD]
[TD]200-399.99[/TD]
[TD]400 or more[/TD]
[/TR]
</tbody>[/TABLE]
Then lookup the corresponding amount in the table. EG. The first example is between 50-199.99. The table states it to be 6 as the result. Then the formula would convert the 6 to 6th.
I found a couple formula's that I think might be able to be combined with an hlookup but I'm not sure.
Where A1 would be the number to be converted into a ranking.
=A1&
IF(AND(MOD(A1,10)<4,OR(A1<10,A1>13)),MID("thstndrd",1+MOD(A1,10)*2,2),"th")
Thank you for taking the time to read.
Last edited: