Nested formula!

D

dunard2

New Member
Joined
Oct 9, 2017
Messages
2
Hello. Below is a not too terribly complicated nested if statement:

=IF(AND(D2>=55000,D2<=66000),IF(AND(C2="A"),K148,IF(AND(C2="B"),K149,IF(AND(C2="C"),K150,IF(AND(C2="D"),K151,IF(AND(C2="E"),K152,IF(AND(C2="F"),K153,0)))))))

What I need to do now is to nest two other nearly identical formulas into this one, but can’t quite seem to get the syntax correct.

The initial condition to be evaluated is the value of cell D2. So the second statement needs to account for a number that is >66000 and <=77000 while the third is for a number that is >77000 and <=88000. No numbers less than 55000 or greater than 88000 need to be evaluated.

The other differences are the reference cells that the formula calls when the “text” conditions are true. So in the case where D2=70000, “A” will reference M148 and in the third, depending on the number in D2, an “A” will reference O148, and so on.

Therefore, depending on the number in D2, the formula will travel down one of three possible logic paths to return a corresponding value contained in columns K, M, or O.
 
Sort by date Sort by votes

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Re: Help with a nested, nested, nested formula!

I wonder if you could share a bit more fun,

I am using this formula, works wonders - but now, I need to remove if cell C3 is > than 89 and Cell F3 = 0

Code: 
=IF(AND(C3 > 90, F3 = 0)),"",IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)))

It works, but, if C3 > 30 and F3 = 0 = it would do the formula
Code: 
IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)))

If you could nudge me again... Thanks
 
Upvote 0
Re: Help with a nested, nested, nested formula!

mrwiley said:
I wonder if you could share a bit more fun,

I am using this formula, works wonders - but now, I need to remove if cell C3 is > than 89 and Cell F3 = 0

Code: 
=IF(AND(C3 > 90, F3 = 0)),"",IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)))

It works, but, if C3 > 30 and F3 = 0 = it would do the formula
Code: 
IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)))

If you could nudge me again... Thanks
Click to expand...

You want it to perform the calculation if the highlighted condition above is met?

Perhaps this:

=IF(AND(C3 > 30, F3 = 0),IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)),"")

This will perform the calculation (F3/E3)-(((F3/E3)-1.05)*2) if this condition is true AND(C3 > 30, F3 = 0). If this condition is not true, then it returns a blank.
 
Upvote 0
Re: Help with a nested, nested, nested formula!

dreid1011 said:
You want it to perform the calculation if the highlighted condition above is met?

Perhaps this:

=IF(AND(C3 > 30, F3 = 0),IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)),"")

This will perform the calculation (F3/E3)-(((F3/E3)-1.05)*2) if this condition is true AND(C3 > 30, F3 = 0). If this condition is not true, then it returns a blank.
Click to expand...

No, it still is failing... :( I am grateful for your attempt. Here is the results.

Code: 
[TABLE="width: 320"]
 <colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]99[/TD]
  [TD="width: 64"]10/13/17[/TD]
  [TD="width: 64, align: right"]360[/TD]
  [TD="width: 64, align: right"]0[/TD]
  [TD="class: xl63, width: 64, align: right"][COLOR=#ff0000]0[/COLOR][/TD]
 [/TR]
 [TR]
  [TD]99[/TD]
  [TD]10/04/17[/TD]
  [TD="align: right"]1900[/TD]
  [TD="align: right"]1900[/TD]
  [TD="class: xl63"]            0[/TD]
 [/TR]
 [TR]
  [TD]99[/TD]
  [TD]10/05/17[/TD]
  [TD="align: right"]1179[/TD]
  [TD="align: right"]1179[/TD]
  [TD="class: xl63"]            0[/TD]
 [/TR]
 [TR]
  [TD]40[/TD]
  [TD]10/13/17[/TD]
  [TD="align: right"]551[/TD]
  [TD="align: right"]360[/TD]
  [TD="class: xl63"]            0[/TD]
[/TR]
</tbody>[/TABLE]

I also have 40's
 
Upvote 0
Re: Help with a nested, nested, nested formula!

Here is the formula in work

Code: 
 [TABLE="width: 1258"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]99[/TD]
[TD]10/13/17[/TD]
[TD]360[/TD]
[TD]0[/TD]
[TD]=IF(AND(C131 > 30, F131 = 0),IF((F131/E131)>1.05,(F131/E131)-(((F131/E131)-1.05)*2),(F131/E131)),"")[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]10/04/17[/TD]
[TD]1900[/TD]
[TD]1900[/TD]
[TD]=IF((F132/E132)>1.05,(F132/E132)-(((F132/E132)-1.05)*2),(F132/E132))[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]10/05/17[/TD]
[TD]1179[/TD]
[TD]1179[/TD]
[TD]=IF((F133/E133)>1.05,(F133/E133)-(((F133/E133)-1.05)*2),(F133/E133))[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]10/13/17[/TD]
[TD]551[/TD]
[TD]360[/TD]
[TD]=IF((F134/E134)>1.05,(F134/E134)-(((F134/E134)-1.05)*2),(F134/E134))[/TD]
[/TR]
</tbody>[/TABLE]

mrwiley said:
No, it still is failing... :( I am grateful for your attempt. Here is the results.

Code: 
[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"]99[/TD]
[TD="width: 64"]10/13/17[/TD]
[TD="width: 64, align: right"]360[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="class: xl63, width: 64, align: right"][COLOR=#ff0000]0[/COLOR][/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]10/04/17[/TD]
[TD="align: right"]1900[/TD]
[TD="align: right"]1900[/TD]
[TD="class: xl63"]            0[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]10/05/17[/TD]
[TD="align: right"]1179[/TD]
[TD="align: right"]1179[/TD]
[TD="class: xl63"]            0[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]10/13/17[/TD]
[TD="align: right"]551[/TD]
[TD="align: right"]360[/TD]
[TD="class: xl63"]            0[/TD]
[/TR]
</tbody>[/TABLE]

I also have 40's
Click to expand...
 
Upvote 0
Re: Help with a nested, nested, nested formula!

What result are you expecting to get from row 131?

0/360 = 0, 0 is NOT greater than 1.05, therefor the formula is returning the result of F131/E131, which is 0.
 
Upvote 0
Re: Help with a nested, nested, nested formula!

I have 4 numbers -

1. Work orders start at status 30
2. Work orders which have parts completed on them turn to status 40
3. Work orders which have been completed and closed but has an issue result in status 90
4. Work orders which are completed and no issues result in status 99

If a WO is at status 30 and 40 - it needs to proceed to calculating the percentage (formula works)
If a WO is at status 90 and 99 without parts being completed (0) then it should result in a blank field. Currently I filter results and delete data with 0 since 0's SKU's the percentage I need to obtain.
If a WO is at status 99 with parts being completed then the it needs to proceed to calculating the percentage (formula works)

Hope this helps, my apologies for not being explanatory earlier. Thank you
 
Upvote 0
Re: Help with a nested, nested, nested formula!

mrwiley said:
I have 4 numbers -

1. Work orders start at status 30
2. Work orders which have parts completed on them turn to status 40
3. Work orders which have been completed and closed but has an issue result in status 90
4. Work orders which are completed and no issues result in status 99

If a WO is at status 30 and 40 - it needs to proceed to calculating the percentage (formula works)
If a WO is at status 90 and 99 without parts being completed (0) then it should result in a blank field. Currently I filter results and delete data with 0 since 0's SKU's the percentage I need to obtain.
If a WO is at status 99 with parts being completed then the it needs to proceed to calculating the percentage (formula works)

Hope this helps, my apologies for not being explanatory earlier. Thank you
Click to expand...

Ok, see if this gets you where you want to be:

=IF(OR(C3=30,C3=40,AND(F3>0,OR(C3=90,C3=99))),IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)),"")
 
Upvote 0
Re: Help with a nested, nested, nested formula!

dreid1011 said:
Ok, see if this gets you where you want to be:

=IF(OR(C3=30,C3=40,AND(F3>0,OR(C3=90,C3=99))),IF((F3/E3)>1.05,(F3/E3)-(((F3/E3)-1.05)*2),(F3/E3)),"")
Click to expand...

Thank you for the follow-up. This didn't work... I will attach a sheet maybe that will help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Nested IF Statement
2
Replies
10
Views
937
6StringJazzer
6StringJazzer
E
  • Question Question
Complicated Nested If Formula not behaving as expected?
Replies
1
Views
378
Event2020
E
F
  • Question Question
Lock cells based on color generated by conditional formating
Replies
2
Views
1K
flashgordie
F
alinka
  • Question Question
Complicated IF formula (how do I combine 11 if statements)
2 3 4
Replies
31
Views
3K
Zack Barresse
Zack Barresse

Forum statistics

Threads
1,224,012
Messages
6,175,931
Members
452,686
Latest member
midhunjoseph

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top