Excel Formula with Nested IF LEFT AND Functions

0 votes

I made a workable formula using nested IF statements, but I still need to add some additional logic to it. I would be nesting extra statements inside the formula, and I'm not entirely sure how to approach that.

The current formula is:

=IF(LEFT(G3,1)="1","998",IF(LEFT(G3,1)="2","998",IF(LEFT(G3,1)="3","998",IF(LEFT(G3,1)="4","996",IF(LEFT(G3,1)="5","996",K3)))))

This is working fine, but now I need to add the additional logic:

  • If G3 begins with "9" and K3 does not begin with "075", "076", or "089" then cell equals "997"

My failed attempt was something like this:

IF(LEFT(G3,1)="1","998",IF(LEFT(G3,1)="2","998",IF(LEFT(G3,1)="3","998",IF(LEFT(G3,1)="4","996",IF(LEFT(G3,1)="5","996",IF(LEFT(G3,1)="9"(AND(LEFT(K3,3)<>"075"))K3))))))

With this being the attempted nested statement I added:

IF(LEFT(G3,1)="9"(AND(LEFT(K3,3)<>"075"))

For "076" and "089," I would then need to repeat this process a few more times, but I have yet to get it right for just one of these.

I am aware that some VBA code may be used to achieve this, but for this file, I'd like to stay away from VBA and stick to formulas exclusively.

Any help would be appreciated. 

Nov 21, 2022 in Others by Kithuzzz
• 28,520 points
139 views

1 answer to this question.

0 votes

Use this:

=IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998",IF(SUMPRODUCT(--(LEFT(G3,1)={"4","5"}))>0,"996",IF(AND(SUMPRODUCT(--(LEFT(G3,1)="9"))>0,SUMPRODUCT(--(LEFT(K3,3)={"075","076","089"}))=0),"997")))

I hope this helps you.

answered Nov 21, 2022 by narikkadan
• 52,760 points

Related Questions In Others

0 votes
0 answers

Combining MID and LEFT Functions in Excel 2019

I currently have a course name and ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 28,520 points
68 views
0 votes
1 answer

Excel If Functions with Multiple Conditions

Use this: =IF(C3<=0,"Green",IF(C3<500,"Silver",IF(C3<=1499,"Gold","Platinum"))) I added <=0 just in case you have ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 52,760 points
54 views
0 votes
1 answer
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18, 2022 in Others by Edureka
• 13,630 points
105 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 52,760 points
157 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 52,760 points
123 views
0 votes
1 answer

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 52,760 points
139 views
0 votes
1 answer

Return blank cell only if referred cell is blank, but return aging if date is entered

Try this: =IF(ISBLANK(AC2),"",TODAY()-AC2) The TODAY  function automatically refreshes based ...READ MORE

answered Dec 17, 2022 in Others by narikkadan
• 52,760 points
107 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 52,760 points
120 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 52,760 points
135 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP