Formula too Long Using If(Countif())

cinci4017 · Jan 8, 2018

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!

thisoldman · Jan 8, 2018

You don't need all those COUNTIF functions. You have:
=IF(COUNTIF(IP3, 1), do_something_when_TRUE, do_something_when_FALSE)

That's the same as:
=IF(IP3=1, do_something_when_TRUE, do_something_when_FALSE)

Here's the first formula where I removed all the COUNTIF functions that were safe to remove. I've formatted your formula so that it's easier to see the structure:

Code:

=IF(IP3=1,
  IF(KV3=1, 1,
    IF(LK3>0, 2,
      IF(KT3=1, 3,
        IF(IW3=1, 4,
          IF(COUNTIF(IT3:IU3, 1), 5,
            IF(JT3=1, 6,
              IF(COUNTIF(JQ3:JS3, 1), 7,
                IF(COUNTIF(IX3:JB3, 1), 8,
                  IF(KB3=1, 9,
                    IF(IV3=1, 10,
                      IF(KD3=1, 11,
                        IF(COUNTIF(JU3:JZ3, 1), 12,
                          IF(COUNTIF(JC3:JJ3, 1), 13,
                            IF(COUNTIF(KE3:KG3, 1), 14,
                              IF(KA3=1, 15,
                                IF(JK3=1, 16,
                                  FALSE)))))))))))))))), "NA")

Here is the same formula without all the line breaks:

=IF(IP3=1, IF(KV3=1, 1, IF(LK3>0, 2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1, 6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(IV3=1, 10, IF(KD3=1, 11, IF(COUNTIF(JU3:JZ3, 1), 12, IF(COUNTIF(JC3:JJ3, 1), 13, IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, FALSE)))))))))))))))), "NA")

More COUNTIF functions could probably be removed but I don't know enough about your spreadsheet. It would really help to see a few lines of the data. Can you post a sanitized version of perhaps 5 lines of representative data?

I would like to know the answers to these questions:

Question 1
What possible values could be in IP3? 1 or blank, empty string, or zero, or something else?

Question 2
What are the possible values in the other cells in the first formula? I am guessing LK3 could hold something other than 1.

Question 3
Are the tested conditions, except for IP3, exclusive? What I mean is: if L3 is greater than 0, are all the other tested conditions FALSE? I'm looking to get rid of all the nested IFs we can.

To be continued.

thisoldman · Jan 8, 2018

You have a heck of a lot of repetition in the second formula. Again, without seeing the spreadsheet, or at least knowing the possible combinations of values, I'm at a loss to know how to help you condense the formula.

Here's a reformatted version of the second formula, without removing the unneeded COUNTIF functions.

Code:

=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,
...
<-- snip -->
...
              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,
...
<-- snip -->
...
              IF(COUNTIF(JC3:JJ3, 1), 13,
                IF(COUNTIF(KE3:KG3, 1), 14,
                  IF(COUNTIF(KA3, 1), 15,
  IF(COUNTIF(JK3, 1), 16...

FDibbins · Jan 8, 2018

Looks to me like you are only "counting" 1 cell?? If so, why bother to count it, just test it for whatever value you need, then "do" whatever it needs to return?
for example...
=IF(COUNTIF(IP3, 1), IF(COUNTIF(KV3, 1), 1, IF(COUNTIF(LK3, ">0"), 2, IF(COUNTIF(KT3, 1), 3
cvould be
=IF(and(IP3=1,KV3=1), 1, IF(LK3>0, 2, IF(KT3=1,3............

In fact, maybe you could even just use a lookup table?

cinci4017 · Jan 8, 2018

First, thank you. I'm wondering if the formula will still be too long even if we clean up most of the countif functions. I'm not quite sure how to circumvent the repetitions considering the value in column LL3 could be any value from 1-16, so in order to avoid having the formula place the same data point in LL4 that it recognized in LL3, I need each component of the formula to begin after the number it recognizes.

thisoldman said:
You don't need all those COUNTIF functions. You have:
=IF(COUNTIF(IP3, 1), do_something_when_TRUE, do_something_when_FALSE)

That's the same as:
=IF(IP3=1, do_something_when_TRUE, do_something_when_FALSE)

Here's the first formula where I removed all the COUNTIF functions that were safe to remove. I've formatted your formula so that it's easier to see the structure:

Code:

=IF(IP3=1, IF(KV3=1, 1, IF(LK3>0, 2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1, 6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(IV3=1, 10, IF(KD3=1, 11, IF(COUNTIF(JU3:JZ3, 1), 12, IF(COUNTIF(JC3:JJ3, 1), 13, IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, FALSE)))))))))))))))), "NA")

Here is the same formula without all the line breaks:

=IF(IP3=1, IF(KV3=1, 1, IF(LK3>0, 2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1, 6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(IV3=1, 10, IF(KD3=1, 11, IF(COUNTIF(JU3:JZ3, 1), 12, IF(COUNTIF(JC3:JJ3, 1), 13, IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, FALSE)))))))))))))))), "NA")

More COUNTIF functions could probably be removed but I don't know enough about your spreadsheet. It would really help to see a few lines of the data. Can you post a sanitized version of perhaps 5 lines of representative data?

Column FX 1 I am not having any issues with. My trouble is coding for column FX2 based off the values of FX, specifically having column FX2 recognize the value in column FX and then search for the other (subsequent) fractures. FX1 and FX2 will be searching the same dataset, which I listed below the FX1/FX2 example. (*in the dataset below FX2 has no values, numbers are on the right margins of the cell while text are on the left*)

FX1 FX2
1=Head; 2=Spine; 3=Pelvis; 4=Femur; 5=Tib/Fib; 6=Humerus; 7=Radius/Ulna; 8=Ankle; 9=Scapula; 10=Patella; 11=Sacrum/Coccyx; 12=Hand; 13=Foot; 14=Clavicle/Rib/Sternum; 15=Ill-defined fx of UE; 15=Ill-defined fx of LE
NA
1
4
NA
3
2

<tbody>
</tbody>

To generate the data for column FX1 I used the code below, which is by in large binary:

Tibial fracture Fibular fracture Patella fracture Femur fracture Ankle Fracture
0=No; 1=Yes 0=No; 1=Yes 0=No; 1=Yes 0=No; 1=Yes 0=No; 1=Yes
NA NA NA NA NA
0 0 0 0 0
0 0 0 1 0
NA NA NA NA NA
0 0 0 1 0

<tbody>
</tbody>

I would like to know the answers to these questions:

Question 1
What possible values could be in IP3? 1 or blank, empty string, or zero, or something else?
-So mainly I intended to use the second formula I listed, where the main cell being analyzed will be LL3. I listed the formula for how I came up with the values in LL3 since I wanted to apply the same formula to the next column, but starting at the next value after what was reported in LL3. The data in IP3 is binary, all 0 or 1 representing either yes or no. In fact, a majority of the data in this excel file is binary.

Question 2
What are the possible values in the other cells in the first formula? I am guessing LK3 could hold something other than 1.
-You are correct. LK3 is one of the only columns of data that contains values other than 0 or 1. It contains a numerical value based on the level of spine injury.

Question 3
Are the tested conditions, except for IP3, exclusive? What I mean is: if L3 is greater than 0, are all the other tested conditions FALSE? I'm looking to get rid of all the nested IFs we can.
-Unfortunately, no. If the formula recognizes a number n in column LL, I need the formula to then look for all injuries starting at n+1. So if LL3 is has number n contained within its cell, the "solution" could be any value greater than n up to the maximum of 16.

To be continued.

I hope my description gave you a bit more insight. If not, I'll try to divulge as much information as I can without convoluting matters more than I already have!

Thanks!

cinci4017 · Jan 8, 2018

I considered using a lookup table, but I intend on applying the same formula to 3505 cells within the column. If I were to make a table with the formulas I wished to apply (1=Formula A, 2=Formula B....) is there a way to make sure that the cell numbers within that formula will be fluid and represent each individual row I wished to apply them to? My concern is if I make Formula A something like =If(LL3=1, blah blah blah)...., I would need Formula A to then recognize LL4, then LL5 as I drag the formula down to apply it to the some odd 3500 rows I have in my dataset.

cinci4017 · Jan 9, 2018

Essentially my goal is to use the following formula (which I removed a lot of redundant countif statements from), but continuing the pattern after "..." until LL3=15. Would it just be easier to to use VBA?

If(LL3=1, IF(LK3>0, 2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10, IF(KD3=1, 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, If(LL3=2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10, IF(KD3=1, 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, If(LL3=3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10, IF(KD3=1, 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, if(LL3=4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10, IF(KD3=1, 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1, 15, IF(JK3=1, 16, If(LL3=5, F(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10, IF(KD3=1, 11,IF(COUNTIF(JU3:JZ3, 1), 12,IF(COUNTIF(JC3:JJ3, 1), 13,IF(COUNTIF(KE3:KG3, 1), 14, IF(KA3=1), 15, IF(JK3=1, 16,...)))))))))))))))))))))))))))))))))))))))))))))))))))))))

thisoldman · Jan 9, 2018

I'm using the first formula as I work this out. If we get an acceptable substitute for formula 1, we should be able to create a substitute for formula 2.

Formula 2 fails due to the number of nested IF functions—we can only go up to 64 nested IFs in Excel. We can get rid of the IFs by using Boolean arithmetic. This is where Excel changes the boolean value TRUE to the integer 1 and the boolean value FALSE to the integer zero when you use logical tests in an arithmetic operation. Here are some examples:

=42+TRUE returns 43
=42+FALSE returns 42
=42-TRUE returns 41
=42-FALSE returns 42
=42*TRUE returns42
=42*FALSE returns 0
=42/TRUE returns 42
=42/FALSE returns #DIV/0!

For TRUE or FALSE above, you can substitute any expression that evaluates to TRUE or FALSE:
=42*(1+1=2) evaluates to 42,
=42*(1+1=3) evaluates to 0.

This formula returns the same results as formula 1, except for one condition:

Code:

=IF(IP3<>1, "NA",
  17 - MAX((KV3=1)*16, (SUM(LK3)>0)*15, (KT3=1)*14, (IW3=1)*13, (SUM(IT3:IU3)>0)*12, (JT3=1)*11,
           (SUM(JQ3:JS3)>0)*10, (SUM(IX3:JB3)>0)*9, (KB3=1)*8, (IV3=1)*7, (KD3=1)*6, (SUM(JU3:JZ3)>0)*5,
           (SUM(JC3:JJ3)>0)*4, (SUM(KE3:KG3)>0)*3, (KA3=1)*2, (JK3=1)*1))

Except for the test for LK3, I wrote the formula as if there could be a mix of NA, 1, and 0 values on a single line. For LK3, I assumed it could be 0, a positive decimal value, or NA.

Where it's different: if IP3 equals 1 and all the tested values are FALSE, the formula returns the value 17. Could this be a problem?

I also used SUM instead of COUNTIF functions.

thisoldman · Jan 9, 2018

If the formula for LL3 is OK, try this for the second formula:

Code:

=MAX((LL3<3)*(SUM(LK3)>0)*2, (LL3<4)*(KT3=1)*3, (LL3<5)*(IW3=1)*4, (LL3<6)*(SUM(IT3:IU3)>0)*5,
     (LL3<7)*(JT3=1)*6, (LL3<8)*(SUM(JQ3:JS3)>0)*7, (LL3<9)*(SUM(IX3:JB3)>0)*8, (LL3<10)*(KB3=1)*9,
     (LL3<11)*(IV3=1)*10, (LL3<12)*(KD3=1)*11, (LL3<13)*(SUM(JU3:JZ3)>0)*12,
     (LL3<14)*(SUM(JC3:JJ3)>0)*13, (LL3<15)*(SUM(KE3:KG3)>0)*14, (LL3<16)*(KA3=1)*15,
     (LL3<17)*(JK3=1)*16))

Please check that the cell references are correct. In Post No. 7, you have

If(LL3=1, IF(LK3>0, 2, IF(KT3=1, 3, IF(IW3=1, 4, IF(COUNTIF(IT3:IU3, 1), 5, IF(JT3=1 ,6, IF(COUNTIF(JQ3:JS3, 1), 7, IF(COUNTIF(IX3:JB3, 1), 8, IF(KB3=1, 9, IF(V3=1, 10,

Previously, this test used IV3. My formulas all use IV3.

I do hope this works for you.

Formula too Long Using If(Countif())

cinci4017

New Member

Excel Facts

thisoldman

Well-known Member

thisoldman

Well-known Member

FDibbins

Well-known Member

cinci4017

New Member

cinci4017

New Member

cinci4017

New Member

thisoldman

Well-known Member

thisoldman

Well-known Member

Similar threads

Forum statistics

Share this page

FX1	FX2
1=Head; 2=Spine; 3=Pelvis; 4=Femur; 5=Tib/Fib; 6=Humerus; 7=Radius/Ulna; 8=Ankle; 9=Scapula; 10=Patella; 11=Sacrum/Coccyx; 12=Hand; 13=Foot; 14=Clavicle/Rib/Sternum; 15=Ill-defined fx of UE; 15=Ill-defined fx of LE
NA
1
4
NA
3
2

Tibial fracture	Fibular fracture	Patella fracture	Femur fracture	Ankle Fracture
0=No; 1=Yes	0=No; 1=Yes	0=No; 1=Yes	0=No; 1=Yes	0=No; 1=Yes
NA	NA	NA	NA	NA
0	0	0	0	0
0	0	0	1	0
NA	NA	NA	NA	NA
0	0	0	1	0

Formula too Long Using If(Countif())

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock