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?

mtarler's avatar
mtarler
Silver Contributor
Nov 06, 2020

Nikoss  Alternatively I think this works:

 

=CHOOSE(IFERROR(SUM(--((FILTER('Pricing Matrix'!D:E,Sheet1!G4&Sheet1!I4='Pricing Matrix'!B:B&'Pricing Matrix'!C:C,"NA")-EW4)>=0))+2,1),"NA","Over","In accordance with Pricing Matrix","Under")

 

see attached sheet.

Also, you can make use of the IFS() function to eliminate nested IF() statements.

Nikoss's avatar
Nikoss
Copper Contributor
Nov 09, 2020

mtarler Thanks a lot for your response. My version of excel doesn't support the "FILTER" function apparently. Would you be kind enough to give me an example of what the formula would look like with IFS function?

  • 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!

  • 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?

  • 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  Here are 2 versions you can try.  1 using IFS and the other using CHOOSE and SUMPRODUCT:

    =IFS(EW2<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",EW2<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"In accordance with Pricing Matrix",EW2>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over",TRUE,"NA")
    =CHOOSE(SUMPRODUCT((--('Pricing Matrix'!D:D<=EW4)--('Pricing Matrix'!E:E<EW4)+1)*('Pricing Matrix'!B:B=Sheet1!G4)*('Pricing Matrix'!C:C=Sheet1!I4))+1,"NA","Under","In accordance with Pricing Matrix","Over")

     

     

Resources