Forum Discussion

Nikoss's avatar
Nikoss
Copper Contributor
Nov 06, 2020
Solved

Need for more than 64 levels of nested - Simplify formula

Hello guys,   I am not that experienced with Excel so I need your help with this. I have a very long formula with 64 levels of nested and I need to add more which is not allowed. I will copy and pa...
admin
BI & Data Analysis
developer
excel
Excel for web
Excel on mobile
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 09, 2020

    Nikoss I and re-attaching the sheet.  I deleted the other formulas on that sheet in case there was some confusion.  It works just fine for me.  Maybe you could explain a little more what you mean by it giving the wrong results.  Does it sometimes give correct results?  Does it ever change?  Can you give an example of a wrong result and what it gave and what you wanted it to give?

Nikoss's avatar
Nikoss
Copper Contributor
Nov 09, 2020

mtarler Apparently my excel version doesn't support IFS function either. The second formula you sent me though which is supported is giving me the wrong results for some reason even on the sample worksheet you sent me.

 

 

mtarler's avatar
mtarler
Silver Contributor
Nov 09, 2020

Nikoss I and re-attaching the sheet.  I deleted the other formulas on that sheet in case there was some confusion.  It works just fine for me.  Maybe you could explain a little more what you mean by it giving the wrong results.  Does it sometimes give correct results?  Does it ever change?  Can you give an example of a wrong result and what it gave and what you wanted it to give?

  • amamangun's avatar
    amamangun
    Copper Contributor
    Aug 11, 2022

    Need for more than 64 levels of nested - Simplify formula -
    Here's is my formula, its for a quote, for cleaning business,
    For type of cleaning: Standard, Deep or Complete, with the selection of how many Square foot, and the price And form 1 to 5x a week, my code here is only up to 3x a week as its so long..
    It's a very long one and hope there's someone can help me simplify it. Thanks in advance!

    =IF(AND(B7="Standard",C8=2000,E7="1X a week"),I9,IF(AND(B7="Standard",C8=2500,E7="1X a week"),I10,
    IF(AND(B7="Standard",C8=3000,E7="1X a week"),I11,IF(AND(B7="Standard",C8=3500,E7="1X a week"),I12,
    IF(AND(B7="Standard",C8=4000,E7="1X a week"),I13,IF(AND(B7="Standard",C8=4500,E7="1X a week"),I14,
    IF(AND(B7="Standard",C8=5000,E7="1X a week"),I15,IF(AND(B7="Standard",C8=5500,E7="1X a week"),I16,
    IF(AND(B7="Standard",C8=6000,E7="1X a week"),I17,IF(AND(B7="Standard",C8=6500,E7="1X a week"),I18,
    IF(AND(B7="Standard",C8=7000,E7="1X a week"),I19,IF(AND(B7="Standard",C8=7500,E7="1X a week"),I20,
    IF(AND(B7="Standard",C8=8000,E7="1X a week"),I21,IF(AND(B7="Standard",C8=8500,E7="1X a week"),I21,
    IF(AND(B7="Standard",C8=9000,E7="1X a week"),I22,IF(AND(B7="Standard",C8=9500,E7="1X a week"),I23,
    IF(AND(B7="Standard",C8=10000,E7="1X a week"),I24,IF(AND(B7="Standard",C8=10500,E7="1X a week"),I25,
    IF(AND(B7="Standard",C8=11000,E7="1X a week"),I26,IF(AND(B7="Standard",C8=11500,E7="1X a week"),I27,
    IF(AND(B7="Standard",C8=12000,E7="1X a week"),I28,IF(AND(B7="Standard",C8=12500,E7="1X a week"),I29,
    IF(AND(B7="Standard",C8=13000,E7="1X a week"),I30,IF(AND(B7="Standard",C8=13500,E7="1X a week"),I31,
    IF(AND(B7="Standard",C8=14000,E7="1X a week"),I32,IF(AND(B7="Standard",C8=14500,E7="1X a week"),I33,
    IF(AND(B7="Standard",C8=15000,E7="1X a week"),I33,IF(AND(B7="Standard",C8=15500,E7="1X a week"),I34,
    IF(AND(B7="Standard",C8=15500,E7="1X a week"),I35,IF(AND(B7="Standard",C8=16000,E7="1X a week"),I36,
    IF(AND(B7="Standard",C8=16500,E7="1X a week"),I37,IF(AND(B7="Standard",C8=17000,E7="1X a week"),I38,
    IF(AND(B7="Standard",C8=17500,E7="1X a week"),I39,IF(AND(B7="Standard",C8=18000,E7="1X a week"),I40,
    IF(AND(B7="Standard",C8=18500,E7="1X a week"),I41,IF(AND(B7="Standard",C8=19000,E7="1X a week"),I42,
    IF(AND(B7="Standard",C8=19500,E7="1X a week"),I43,IF(AND(B7="Standard",C8=20000,E7="1X a week"),I44,
    IF(AND(B7="Deep",C8=2000,E7="1X a week"),J9,IF(AND(B7="Deep",C8=2500,E7="1X a week"),J10,
    IF(AND(B7="Deep",C8=3000,E7="1X a week"),J11,IF(AND(B7="Deep",C8=3500,E7="1X a week"),J12,
    IF(AND(B7="Deep",C8=4000,E7="1X a week"),J13,IF(AND(B7="Deep",C8=4500,E7="1X a week"),J14,
    IF(AND(B7="Deep",C8=5000,E7="1X a week"),J15,IF(AND(B7="Deep",C8=5500,E7="1X a week"),J16,
    IF(AND(B7="Deep",C8=6000,E7="1X a week"),J17,IF(AND(B7="Deep",C8=6500,E7="1X a week"),J18,
    IF(AND(B7="Deep",C8=7000,E7="1X a week"),J19,IF(AND(B7="Deep",C8=7500,E7="1X a week"),J20,
    IF(AND(B7="Deep",C8=8000,E7="1X a week"),J21,IF(AND(B7="Deep",C8=8500,E7="1X a week"),J21,
    IF(AND(B7="Deep",C8=9000,E7="1X a week"),J22,IF(AND(B7="Deep",C8=9500,E7="1X a week"),J23,
    IF(AND(B7="Deep",C8=10000,E7="1X a week"),J24,IF(AND(B7="Deep",C8=10500,E7="1X a week"),J25,
    IF(AND(B7="Deep",C8=11000,E7="1X a week"),J26,IF(AND(B7="DeepC8=11500,E7="1X a week"),J27,
    IF(AND(B7="Deep",C8=12000,E7="1X a week"),J28,IF(AND(B7="Deep",C8=12500,E7="1X a week"),J29,
    IF(AND(B7="Deep",C8=13000,E7="1X a week"),J30,IF(AND(B7="Deep",C8=13500,E7="1X a week"),J31,
    IF(AND(B7="Deep",C8=14000,E7="1X a week"),J32,IF(AND(B7="Deep",C8=14500,E7="1X a week"),J33,
    IF(AND(B7="Deep",C8=15000,E7="1X a week"),J33,IF(AND(B7="Deep",C8=15500,E7="1X a week"),J34,
    IF(AND(B7="Deep",C8=15500,E7="1X a week"),J35,IF(AND(B7="Deep",C8=16000,E7="1X a week"),J36,
    IF(AND(B7="Deep",C8=16500,E7="1X a week"),J37,IF(AND(B7="Deep",C8=17000,E7="1X a week"),J38,
    IF(AND(B7="Deep",C8=17500,E7="1X a week"),J39,IF(AND(B7="Deep",C8=18000,E7="1X a week"),J40,
    IF(AND(B7="Deep",C8=18500,E7="1X a week"),J41,IF(AND(B7="Deep",C8=19000,E7="1X a week"),J42,
    IF(AND(B7="Deep",C8=19500,E7="1X a week"),J43,IF(AND(B7="Deep",C8=20000,E7="1X a week"),J44,
    IF(AND(B7="Complete",C8=2000,E7="1X a week"),K9,IF(AND(B7=" Complete ",C8=2500,E7="1X a week"),K10,
    IF(AND(B7=" Complete ",C8=3000,E7="1X a week"),K11,IF(AND(B7=" Complete ",C8=3500,E7="1X a week"),K12,
    IF(AND(B7=" Complete ",C8=4000,E7="1X a week"),K13,IF(AND(B7=" Complete ",C8=4500,E7="1X a week"),K14,
    IF(AND(B7=" Complete ",C8=5000,E7="1X a week"),K15,IF(AND(B7=" Complete ",C8=5500,E7="1X a week"),K16,
    IF(AND(B7=" Complete ",C8=6000,E7="1X a week"),K17,IF(AND(B7=" Complete ",C8=6500,E7="1X a week"),K18,
    IF(AND(B7=" Complete ",C8=7000,E7="1X a week"),K19,IF(AND(B7=" Complete ",C8=7500,E7="1X a week"),K20,
    IF(AND(B7=" Complete ",C8=8000,E7="1X a week"),K21,IF(AND(B7=" Complete ",C8=8500,E7="1X a week"),K21,
    IF(AND(B7=" Complete ",C8=9000,E7="1X a week"),K22,IF(AND(B7=" Complete ",C8=9500,E7="1X a week"),K23,
    IF(AND(B7=" Complete ",C8=10000,E7="1X a week"),K24,IF(AND(B7=" Complete ",C8=10500,E7="1X a week"),K25,
    IF(AND(B7=" Complete ",C8=11000,E7="1X a week"),K26,IF(AND(B7=" Complete ",C8=11500,E7="1X a week"),K27,
    IF(AND(B7=" Complete ",C8=12000,E7="1X a week"),K28,IF(AND(B7=" Complete ",C8=12500,E7="1X a week"),K29,
    IF(AND(B7=" Complete ",C8=13000,E7="1X a week"),K30,IF(AND(B7=" Complete ",C8=13500,E7="1X a week"),K31,
    IF(AND(B7=" Complete ",C8=14000,E7="1X a week"),K32,IF(AND(B7=" Complete ",C8=14500,E7="1X a week"),K33,
    IF(AND(B7=" Complete ",C8=15000,E7="1X a week"),K33,IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K34,
    IF(AND(B7=" Complete ",C8=15500,E7="1X a week"),K35,IF(AND(B7=" Complete ",C8=16000,E7="1X a week"),K36,
    IF(AND(B7=" Complete ",C8=16500,E7="1X a week"),K37,IF(AND(B7=" Complete ",C8=17000,E7="1X a week"),K38,
    IF(AND(B7=" Complete ",C8=17500,E7="1X a week"),K39,IF(AND(B7=" Complete ",C8=18000,E7="1X a week"),K40,
    IF(AND(B7=" Complete ",C8=18500,E7="1X a week"),K41,IF(AND(B7=" Complete ",C8=19000,E7="1X a week"),K42,
    IF(AND(B7=" Complete ",C8=19500,E7="1X a week"),K43,IF(AND(B7=" Complete ",C8=20000,E7="1X a week"),K44,

    IF(AND(B7="Standard",C8=2000,E7="2X a week"),M9,IF(AND(B7="Standard",C8=2500,E7="2X a week"),M10,
    IF(AND(B7="Standard",C8=3000,E7="2X a week"),M11,IF(AND(B7="Standard",C8=3500,E7="2X a week"),M12,
    IF(AND(B7="Standard",C8=4000,E7="2X a week"),M13,IF(AND(B7="Standard",C8=4500,E7="2X a week"),M14,
    IF(AND(B7="Standard",C8=5000,E7="2X a week"),M15,IF(AND(B7="Standard",C8=5500,E7="2X a week"),M16,
    IF(AND(B7="Standard",C8=6000,E7="2X a week"),M17,IF(AND(B7="Standard",C8=6500,E7="2X a week"),M18,
    IF(AND(B7="Standard",C8=7000,E7="2X a week"),M19,IF(AND(B7="Standard",C8=7500,E7="2X a week"),M20,
    IF(AND(B7="Standard",C8=8000,E7="2X a week"),M21,IF(AND(B7="Standard",C8=8500,E7="2X a week"),M21,
    IF(AND(B7="Standard",C8=9000,E7="2X a week"),M22,IF(AND(B7="Standard",C8=9500,E7="2X a week"),IM23,
    IF(AND(B7="Standard",C8=10000,E7="2X a week"),M24,IF(AND(B7="Standard",C8=10500,E7="2X a week"),M25,
    IF(AND(B7="Standard",C8=11000,E7="2X a week"),M26,IF(AND(B7="Standard",C8=11500,E7="2X a week"),M27,
    IF(AND(B7="Standard",C8=12000,E7="2X a week"),M28,IF(AND(B7="Standard",C8=12500,E7="2X a week"),M29,
    IF(AND(B7="Standard",C8=13000,E7="2X a week"),M30,IF(AND(B7="Standard",C8=13500,E7="2X a week"),M31,
    IF(AND(B7="Standard",C8=14000,E7="2X a week"),M32,IF(AND(B7="Standard",C8=14500,E7="2X a week"),M33,
    IF(AND(B7="Standard",C8=15000,E7="2X a week"),M33,IF(AND(B7="Standard",C8=15500,E7="2X a week"),M34,
    IF(AND(B7="Standard",C8=15500,E7="2X a week"),M35,IF(AND(B7="Standard",C8=16000,E7="2X a week"),M36,
    IF(AND(B7="Standard",C8=16500,E7="2X a week"),M37,IF(AND(B7="Standard",C8=17000,E7="2X a week"),M38,
    IF(AND(B7="Standard",C8=17500,E7="2X a week"),M39,IF(AND(B7="Standard",C8=18000,E7="2X a week"),M40,
    IF(AND(B7="Standard",C8=18500,E7="2X a week"),M41,IF(AND(B7="Standard",C8=19000,E7="2X a week"),M42,
    IF(AND(B7="Standard",C8=19500,E7="2X a week"),M43,IF(AND(B7="Standard",C8=20000,E7="2X a week"),M44,
    IF(AND(B7="Deep",C8=2000,E7="2X a week"),N9,IF(AND(B7="Deep",C8=2500,E7="2X a week"),N10,
    IF(AND(B7="Deep",C8=3000,E7="2X a week"),N11,IF(AND(B7="Deep",C8=3500,E7="2X a week"),N12,
    IF(AND(B7="Deep",C8=4000,E7="2X a week"),N13,IF(AND(B7="Deep",C8=4500,E7="2X a week"),N14,
    IF(AND(B7="Deep",C8=5000,E7="2X a week"),N15,IF(AND(B7="Deep",C8=5500,E7="2X a week"),N16,
    IF(AND(B7="Deep",C8=6000,E7="2X a week"),N17,IF(AND(B7="Deep",C8=6500,E7="2X a week"),N18,
    IF(AND(B7="Deep",C8=7000,E7="2X a week"),N19,IF(AND(B7="Deep",C8=7500,E7="2X a week"),N20,
    IF(AND(B7="Deep",C8=8000,E7="2X a week"),N21,IF(AND(B7="Deep",C8=8500,E7="2X a week"),N21,
    IF(AND(B7="Deep",C8=9000,E7="2X a week"),N22,IF(AND(B7="Deep",C8=9500,E7="2X a week"),JN23,
    IF(AND(B7="Deep",C8=10000,E7="2X a week"),N24,IF(AND(B7="Deep",C8=10500,E7="2X a week"),N25,
    IF(AND(B7="Deep",C8=11000,E7="2X a week"),N26,IF(AND(B7="DeepC8=11500,E7="2X a week"),N27,
    IF(AND(B7="Deep",C8=12000,E7="2X a week"),N28,IF(AND(B7="Deep",C8=12500,E7="2X a week"),N29,
    IF(AND(B7="Deep",C8=13000,E7="2X a week"),N30,IF(AND(B7="Deep",C8=13500,E7="2X a week"),N31,
    IF(AND(B7="Deep",C8=14000,E7="2X a week"),N32,IF(AND(B7="Deep",C8=14500,E7="2X a week"),N33,
    IF(AND(B7="Deep",C8=15000,E7="2X a week"),N33,IF(AND(B7="Deep",C8=15500,E7="2X a week"),N34,
    IF(AND(B7="Deep",C8=15500,E7="2X a week"),N35,IF(AND(B7="Deep",C8=16000,E7="2X a week"),N36,
    IF(AND(B7="Deep",C8=16500,E7="2X a week"),N37,IF(AND(B7="Deep",C8=17000,E7="2X a week"),N38,
    IF(AND(B7="Deep",C8=17500,E7="2X a week"),N39,IF(AND(B7="Deep",C8=18000,E7="1X a week"),N40,
    IF(AND(B7="Deep",C8=18500,E7="2X a week"),N41,IF(AND(B7="Deep",C8=19000,E7="2X a week"),N42,
    IF(AND(B7="Deep",C8=19500,E7="2X a week"),N43,IF(AND(B7="Deep",C8=20000,E7="1X a week"),N44,
    IF(AND(B7="Complete",C8=2000,E7="2X a week"),O9,IF(AND(B7=" Complete ",C8=2500,E7="1X a week"),O10,
    IF(AND(B7=" Complete ",C8=3000,E7="2X a week"),O11,IF(AND(B7=" Complete ",C8=3500,E7="2X a week"),O12,
    IF(AND(B7=" Complete ",C8=4000,E7="2X a week"),O13,IF(AND(B7=" Complete ",C8=4500,E7="2X a week"),O14,
    IF(AND(B7=" Complete ",C8=5000,E7="2X a week"),O15,IF(AND(B7=" Complete ",C8=5500,E7="2X a week"),O16,
    IF(AND(B7=" Complete ",C8=6000,E7="2X a week"),O17,IF(AND(B7=" Complete ",C8=6500,E7="2X a week"),O18,
    IF(AND(B7=" Complete ",C8=7000,E7="2X a week"),O19,IF(AND(B7=" Complete ",C8=7500,E7="2X a week"),O20,
    IF(AND(B7=" Complete ",C8=8000,E7="2X a week"),O21,IF(AND(B7=" Complete ",C8=8500,E7="2X a week"),O21,
    IF(AND(B7=" Complete ",C8=9000,E7="2X a week"),O22,IF(AND(B7=" Complete ",C8=9500,E7="2X a week"),O23,
    IF(AND(B7=" Complete ",C8=10000,E7="2X a week"),O24,IF(AND(B7=" Complete ",C8=10500,E7="2X a week"),O25,
    IF(AND(B7=" Complete ",C8=11000,E7="2X a week"),O26,IF(AND(B7=" Complete ",C8=11500,E7="2X a week"),O27,
    IF(AND(B7=" Complete ",C8=12000,E7="2X a week"),O28,IF(AND(B7=" Complete ",C8=12500,E7="2X a week"),O29,
    IF(AND(B7=" Complete ",C8=13000,E7="2X a week"),O30,IF(AND(B7=" Complete ",C8=13500,E7="2X a week"),O31,
    IF(AND(B7=" Complete ",C8=14000,E7="2X a week"),O32,IF(AND(B7=" Complete ",C8=14500,E7="2X a week"),O33,
    IF(AND(B7=" Complete ",C8=15000,E7="2X a week"),O33,IF(AND(B7=" Complete ",C8=15500,E7="2X a week"),O34,
    IF(AND(B7=" Complete ",C8=15500,E7="2X a week"),O35,IF(AND(B7=" Complete ",C8=16000,E7="2X a week"),O36,
    IF(AND(B7=" Complete ",C8=16500,E7="2X a week"),O37,IF(AND(B7=" Complete ",C8=17000,E7="2X a week"),O38,
    IF(AND(B7=" Complete ",C8=17500,E7="2X a week"),O39,IF(AND(B7=" Complete ",C8=18000,E7="2X a week"),O40,
    IF(AND(B7=" Complete ",C8=18500,E7="2X a week"),O41,IF(AND(B7=" Complete ",C8=19000,E7="2X a week"),O42,
    IF(AND(B7=" Complete ",C8=19500,E7="2X a week"),O43,IF(AND(B7=" Complete ",C8=20000,E7="2X a week"),O44,

    IF(AND(B7="Standard",C8=2000,E7="3X a week"),Q9,IF(AND(B7="Standard",C8=2500,E7="1X a week"),Q10,
    IF(AND(B7="Standard",C8=3000,E7="3X a week"),Q11,IF(AND(B7="Standard",C8=3500,E7="1X a week"),Q12,
    IF(AND(B7="Standard",C8=4000,E7="3X a week"),Q13,IF(AND(B7="Standard",C8=4500,E7="1X a week"),Q14,
    IF(AND(B7="Standard",C8=5000,E7="3X a week"),Q15,IF(AND(B7="Standard",C8=5500,E7="1X a week"),Q16,
    IF(AND(B7="Standard",C8=6000,E7="3X a week"),Q17,IF(AND(B7="Standard",C8=6500,E7="1X a week"),Q18,
    IF(AND(B7="Standard",C8=7000,E7="3X a week"),Q19,IF(AND(B7="Standard",C8=7500,E7="1X a week"),Q20,
    IF(AND(B7="Standard",C8=8000,E7="3X a week"),Q21,IF(AND(B7="Standard",C8=8500,E7="1X a week"),Q21,
    IF(AND(B7="Standard",C8=9000,E7="3X a week"),Q22,IF(AND(B7="Standard",C8=9500,E7="1X a week"),Q23,
    IF(AND(B7="Standard",C8=10000,E7="3X a week"),Q24,IF(AND(B7="Standard",C8=10500,E7="1X a week"),Q25,
    IF(AND(B7="Standard",C8=11000,E7="3X a week"),Q26,IF(AND(B7="Standard",C8=11500,E7="1X a week"),Q27,
    IF(AND(B7="Standard",C8=12000,E7="3X a week"),Q28,IF(AND(B7="Standard",C8=12500,E7="1X a week"),Q29,
    IF(AND(B7="Standard",C8=13000,E7="3X a week"),Q30,IF(AND(B7="Standard",C8=13500,E7="1X a week"),Q31,
    IF(AND(B7="Standard",C8=14000,E7="3X a week"),Q32,IF(AND(B7="Standard",C8=14500,E7="1X a week"),Q33,
    IF(AND(B7="Standard",C8=15000,E7="3X a week"),Q33,IF(AND(B7="Standard",C8=15500,E7="1X a week"),Q34,
    IF(AND(B7="Standard",C8=15500,E7="3X a week"),Q35,IF(AND(B7="Standard",C8=16000,E7="1X a week"),Q36,
    IF(AND(B7="Standard",C8=16500,E7="3X a week"),Q37,IF(AND(B7="Standard",C8=17000,E7="1X a week"),Q38,
    IF(AND(B7="Standard",C8=17500,E7="3X a week"),Q39,IF(AND(B7="Standard",C8=18000,E7="1X a week"),Q40,
    IF(AND(B7="Standard",C8=18500,E7="3X a week"),Q41,IF(AND(B7="Standard",C8=19000,E7="1X a week"),Q42,
    IF(AND(B7="Standard",C8=19500,E7="3X a week"),Q43,IF(AND(B7="Standard",C8=20000,E7="1X a week"),Q44,
    IF(AND(B7="Deep",C8=2000,E7="3X a week"),R9,IF(AND(B7="Deep",C8=2500,E7="3X a week"),R10,
    IF(AND(B7="Deep",C8=3000,E7="3X a week"),R11,IF(AND(B7="Deep",C8=3500,E7="3X a week"),R12,
    IF(AND(B7="Deep",C8=4000,E7="3X a week"),R13,IF(AND(B7="Deep",C8=4500,E7="3X a week"),R14,
    IF(AND(B7="Deep",C8=5000,E7="3X a week"),R15,IF(AND(B7="Deep",C8=5500,E7="3X a week"),R16,
    IF(AND(B7="Deep",C8=6000,E7="3X a week"),R17,IF(AND(B7="Deep",C8=6500,E7="3X a week"),R18,
    IF(AND(B7="Deep",C8=7000,E7="3X a week"),R19,IF(AND(B7="Deep",C8=7500,E7="3X a week"),R20,
    IF(AND(B7="Deep",C8=8000,E7="3X a week"),R21,IF(AND(B7="Deep",C8=8500,E7="3X a week"),R21,
    IF(AND(B7="Deep",C8=9000,E7="3X a week"),R22,IF(AND(B7="Deep",C8=9500,E7="3X a week"),R23,
    IF(AND(B7="Deep",C8=10000,E7="3X a week"),R24,IF(AND(B7="Deep",C8=10500,E7="3X a week"),R25,
    IF(AND(B7="Deep",C8=11000,E7="3X a week"),R26,IF(AND(B7="DeepC8=11500,E7="3X a week"),R27,
    IF(AND(B7="Deep",C8=12000,E7="3X a week"),R28,IF(AND(B7="Deep",C8=12500,E7="3X a week"),R29,
    IF(AND(B7="Deep",C8=13000,E7="3X a week"),R30,IF(AND(B7="Deep",C8=13500,E7="3X a week"),R31,
    IF(AND(B7="Deep",C8=14000,E7="3X a week"),R32,IF(AND(B7="Deep",C8=14500,E7="3X a week"),R33,
    IF(AND(B7="Deep",C8=15000,E7="3X a week"),R33,IF(AND(B7="Deep",C8=15500,E7="3X a week"),R34,
    IF(AND(B7="Deep",C8=15500,E7="3X a week"),R35,IF(AND(B7="Deep",C8=16000,E7="3X a week"),R36,
    IF(AND(B7="Deep",C8=16500,E7="3X a week"),R37,IF(AND(B7="Deep",C8=17000,E7="3X a week"),R38,
    IF(AND(B7="Deep",C8=17500,E7="3X a week"),R39,IF(AND(B7="Deep",C8=18000,E7="3X a week"),R40,
    IF(AND(B7="Deep",C8=18500,E7="3X a week"),R41,IF(AND(B7="Deep",C8=19000,E7="3X a week"),R42,
    IF(AND(B7="Deep",C8=19500,E7="3X a week"),R43,IF(AND(B7="Deep",C8=20000,E7="3X a week"),R44,
    IF(AND(B7="Complete",C8=2000,E7="3X a week"),S9,IF(AND(B7=" Complete ",C8=2500,E7="3X a week"),S10,
    IF(AND(B7=" Complete ",C8=3000,E7="3X a week"),S11,IF(AND(B7=" Complete ",C8=3500,E7="3X a week"),S12,
    IF(AND(B7=" Complete ",C8=4000,E7="3X a week"),S13,IF(AND(B7=" Complete ",C8=4500,E7="3X a week"),S14,
    IF(AND(B7=" Complete ",C8=5000,E7="3X a week"),S15,IF(AND(B7=" Complete ",C8=5500,E7="3X a week"),S16,
    IF(AND(B7=" Complete ",C8=6000,E7="3X a week"),S17,IF(AND(B7=" Complete ",C8=6500,E7="3X a week"),S18,
    IF(AND(B7=" Complete ",C8=7000,E7="3X a week"),S19,IF(AND(B7=" Complete ",C8=7500,E7="3X a week"),S20,
    IF(AND(B7=" Complete ",C8=8000,E7="3X a week"),S21,IF(AND(B7=" Complete ",C8=8500,E7="3X a week"),S21,
    IF(AND(B7=" Complete ",C8=9000,E7="3X a week"),S22,IF(AND(B7=" Complete ",C8=9500,E7="3X a week"),S23,
    IF(AND(B7=" Complete ",C8=10000,E7="3X a week"),S24,IF(AND(B7=" Complete ",C8=10500,E7="3X a week"),S25,
    IF(AND(B7=" Complete ",C8=11000,E7="3X a week"),S26,IF(AND(B7=" Complete ",C8=11500,E7="3X a week"),S27,
    IF(AND(B7=" Complete ",C8=12000,E7="3X a week"),S28,IF(AND(B7=" Complete ",C8=12500,E7="3X a week"),S29,
    IF(AND(B7=" Complete ",C8=13000,E7="3X a week"),S30,IF(AND(B7=" Complete ",C8=13500,E7="3X a week"),S31,
    IF(AND(B7=" Complete ",C8=14000,E7="3X a week"),S32,IF(AND(B7=" Complete ",C8=14500,E7="3X a week"),S33,
    IF(AND(B7=" Complete ",C8=15000,E7="3X a week"),S33,IF(AND(B7=" Complete ",C8=15500,E7="3X a week"),S34,
    IF(AND(B7=" Complete ",C8=15500,E7="3X a week"),S35,IF(AND(B7=" Complete ",C8=16000,E7="3X a week"),S36,
    IF(AND(B7=" Complete ",C8=16500,E7="3X a week"),S37,IF(AND(B7=" Complete ",C8=17000,E7="3X a week"),S38,
    IF(AND(B7=" Complete ",C8=17500,E7="3X a week"),S39,IF(AND(B7=" Complete ",C8=18000,E7="3X a week"),S40,
    IF(AND(B7=" Complete ",C8=18500,E7="3X a week"),S41,IF(AND(B7=" Complete ",C8=19000,E7="3X a week"),S42,
    IF(AND(B7=" Complete ",C8=19500,E7="3X a week"),S43,IF(AND(B7=" Complete ",C8=20000,E7="3X a week"),S44,
    ""))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

  • Nikoss's avatar
    Nikoss
    Copper Contributor
    Nov 09, 2020

    mtarler Yesssssss this works!!!!!!!!!!!! Thank you soooo much!!!!! I have been trying to figure this out for almost a week! I really really appreciate your help!

Resources