Hi all,
I am trying to compile a formula that recognizes a given value in column LL (starting at LL3) and then performs a search of specific cells within that row, reporting data in a descending fashion for a hierarchy I have created. Basically, I have column LL with numbers between 1-16, which stand for a certain injury. I have the injuries characterized by severity, with 1 being the most severe. I am trying to create a formula that recognizes the number in LL, then performs a search to report the next most severe injury. For example, if 5 is recognized, then I need the formula to evaluate for 6-16.
I successfully developed values for column LL using the following formula
=IF(COUNTIF(IP3, 1), IF(COUNTIF(KV3, 1), 1, IF(COUNTIF(LK3, ">0"), 2, IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16,"FALSE")))))))))))))))), "NA")
However, when trying to code for the next most severe injuries in the next column over my formula is too long. What I had been doing was using a long-winded series if If(countif()) formulas. For example, my formula looked like:
=If(countif(LL3, 1), IF(COUNTIF(LK3, ">0"), 2, IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16, If(countif(LL3, 2), IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16, If(countif(LL3, 3), IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16.......
Essentially I am asking excel to search for the number 1 in LL3, then search for the following injuries in descending order, then instead of truncating I am having the formula do the same for each subsequent injury in descending order. This is functionally what I would like to do, but my formula is too long for excel to accept it. The issue I am having is the values in my LL column range from 1-16 so need a way for excel to acknowledge the values in LL so that I do not get repeating values in the next column over.
I'm at a block for how to overcome this. I was initially thinking I could create individual formulas for numbers 1-16 in a distant cell block, but am not sure how to tell excel to run those formulas based off the number it recognizes in LL while still maintaining the search within the same row that it recognized the value in the LL cell.
Any advice or insight would be vastly appreciated.
Thanks!
I am trying to compile a formula that recognizes a given value in column LL (starting at LL3) and then performs a search of specific cells within that row, reporting data in a descending fashion for a hierarchy I have created. Basically, I have column LL with numbers between 1-16, which stand for a certain injury. I have the injuries characterized by severity, with 1 being the most severe. I am trying to create a formula that recognizes the number in LL, then performs a search to report the next most severe injury. For example, if 5 is recognized, then I need the formula to evaluate for 6-16.
I successfully developed values for column LL using the following formula
=IF(COUNTIF(IP3, 1), IF(COUNTIF(KV3, 1), 1, IF(COUNTIF(LK3, ">0"), 2, IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16,"FALSE")))))))))))))))), "NA")
However, when trying to code for the next most severe injuries in the next column over my formula is too long. What I had been doing was using a long-winded series if If(countif()) formulas. For example, my formula looked like:
=If(countif(LL3, 1), IF(COUNTIF(LK3, ">0"), 2, IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16, If(countif(LL3, 2), IF(COUNTIF(KT3, 1), 3, IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16, If(countif(LL3, 3), IF(COUNTIF(IW3, 1), 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(COUNTIF(JT3, 1),6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(COUNTIF(KB3, 1), 9, IF(COUNTIF(IV3, 1), 10, IF(COUNTIF(KD3, 1), 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(COUNTIF(KA3, 1), 15, IF(COUNTIF(JK3, 1), 16.......
Essentially I am asking excel to search for the number 1 in LL3, then search for the following injuries in descending order, then instead of truncating I am having the formula do the same for each subsequent injury in descending order. This is functionally what I would like to do, but my formula is too long for excel to accept it. The issue I am having is the values in my LL column range from 1-16 so need a way for excel to acknowledge the values in LL so that I do not get repeating values in the next column over.
I'm at a block for how to overcome this. I was initially thinking I could create individual formulas for numbers 1-16 in a distant cell block, but am not sure how to tell excel to run those formulas based off the number it recognizes in LL while still maintaining the search within the same row that it recognized the value in the LL cell.
Any advice or insight would be vastly appreciated.
Thanks!