Using IF/AND in Nested IF

User avatar
BobH
UraniumLounger
Posts: 9218
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Using IF/AND in Nested IF

Post by BobH »

I've looked at this problem too long and now need help to look at it clearly.

I have and IF/AND statement that works perfectly. I copy and pasted it into rows in a column; but if one of the dependent cells is blank, I get a #VALUE result. So, it occurred to me that I could precede it with an IF statement that checks to see that the dependent cell is blank and skip the calculations if it is.

The IF/AND statement reads: =IF(AND(CONDITION1,CONDITION2),(CALCULATION_IF_BOTH_CONDITIONS_ARE_TRUE),(CALCULATION_IF_FIRST_CONDITION_IS_TRUE) or, as entered, =IF(AND($M2>0,$M2>1000),(($M2*$S$7)+($A2*$S$8))*(1+$R$9)-100,($M2*$S$8)+($A2*$R$9)). It produces a correct result under both conditions but produces the #VALUE if the $M2 cell is blank.

I tried to add =IF(DEPENDENT_CELL_ISNOT_BLANK) execute the formula above, else, make cell blank; or =IF($M2<>"",IF(AND($M2>0,$M2>1000),(($M2*$S$7)+($A2*$S$8))*(1+$R$9)-100,($M2*$S$8)+($A2*$R$9),"").

I get an error message without any hints as to what is wrong. I could not find any information in my searches that related to nested IF statements when one of them is an IF/AND.

Any help with this will be greatly appreciated.

:cheers: :chocciebar: :thankyou:
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

PJ_in_FL
5StarLounger
Posts: 1090
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Using IF/AND in Nested IF

Post by PJ_in_FL »

Have you considered using IFERROR(ifstatement,"") instead?
PJ in (usually sunny) FL

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Using IF/AND in Nested IF

Post by ChrisGreaves »

BobH wrote:
27 Jan 2023, 23:26
I've looked at this problem too long and now need help to look at it clearly.
(snip!)
Any help with this will be greatly appreciated.
I'll do anything for appreciation :grin:

My first thought is "way too complex", but then I must assume that you have a need for this.
I develop such complex statments by building the "core" parts in separate cells. Your core parts might be sub-statements such as the bit that reads "(($M2*$S$7)+($A2*$S$8))*(1+$R$9)-100" or similar. Presumably this produces some numeric result, might be "2000"

I do that for each part of the expression, then build the full statement as,
=IF($M2<>"", <first numeric result, second numeric result)
and make sure that that works.
Then I gradually replace each "result" with the corresponding sub-statement, noting when the final result changes (means I have introduced a faulty statement).

I took your IF statement and pasted it straight into a new worksheet
Untitled.png
Straight off I noticed that (cell A1) appears to be taken in as pure text. It does not return a numeric result, or an error value. It seems to be taken as a string.
That suggests unbalanced quotes or similar.
Then I decomposed it, yanking out sub-statements until the third such, when Excel2003 pounced upon it!

It is way past my bed time, but I'll try a more complete analysis tomorrow.


Basically, implement each small part of your complex statement independently, the statement in column A and its expected result in column B. This is somewhat like my pet thing about "self-testing functions", I know.

Cheers, Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using IF/AND in Nested IF

Post by HansV »

Try

=IF($M2<>"",IF(AND($M2>0,$M2>1000),(($M2*$S$7)+($A2*$S$8))*(1+$R$9)-100,($M2*$S$8)+($A2*$R$9)),"")
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Using IF/AND in Nested IF

Post by ChrisGreaves »

BobH wrote:
27 Jan 2023, 23:26
I've looked at this problem too long and now need help to look at it clearly.
To look at it clearly:-
From my point of view you are using absolute cell references such as "A2" and "S8" which have little meaning in my brain. Perhaps you have, alongside those cells, meaningful labels such as "Net Tax" and "Gross Income" which assist your brain in understanding the formula.
In that sense I am left operating more like a computer than a human; not a bad thing, as it turns out!

In the attached sheet I first entered data in the cells, trivial data starting at "1" and ending at "5". Easy for me to do mental arithmetic.

Then I pasted in your formula from the post and - unlike last night - I got a value of "17". Now in my way, I would have satisfied myself by hand BEFORE entering the formula that with the given values 1 through 5 I should get a result 17. That's what you and I know as Acceptance Testing, and it's what I use as the basis for self-testing procedures in VBA (as in "Debug.Assert 17= ...")

Question 1 to you: Would you expect EXACTLY 17 as a result of the given data?

Then I selected and deleted the cells A1:S9. You stated that if one of the cells was null you got a VALUE error, so I thought that if they were all NULL then at least one of them was null. But that gave me a value of Zero.

Question 2 to you: Would you expect a value of zero if all cells were null?

Then I got down to work.

I copied the innermost expression and pasted it into cell L10 preceded by an equal sign. In the cell to the right I pasted a shorthand form of label. Since the expression used cells A2 and R9, I made a label "A2R9"
And continued for seven sub-expressions, in my worksheet L10:L16.

I used Insert, Name, Create, Right column to assign the six names (in column M) to the six cells (in column L). Now I can refer to innermost expressions by a symbolic name.
In doing this I observed the test for M2 being greater than 0 AND M2 being greater than 1000.

Question 3 to you: If M2 is greater than 1000, how can it ever NOT be greater than zero? Just askin' ... :innocent:

Then I did two, two, and one more sub-expression as shown in rows 18 through 26.

Finally I built the original expression using just my sub-expressions which, mirable dictu, in cell L26, gave me the same answer as the full statement up in cell L1, which I found to be encouraging.

Question 4 to you: Armed with this analyzed expression, are you now able to duplicate your VALUE error using your real data?

You may want to load cells N10:N26 with YOUR meaningful labels (such as "Net Tax" and "Gross Income" ) if it helps us all communicate in human terms.

I see that Hans has supplied a corrected expression which I have not tried, but too you might consider loading Hans's expression into my Book1.xls and see if it generates my results. A testing statement might be along the lines of

Code: Select all

=IF(L26=Hans,"OK!","Bad")
And similar testing statements might be in order for the sub-expressions.

Your loving nephew, Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Using IF/AND in Nested IF

Post by ChrisGreaves »

ChrisGreaves wrote:
28 Jan 2023, 11:03
And similar testing statements might be in order for the sub-expressions.
P.S. Too, if you type a space character into one of the five data cells, then do you get a VALUE error?
Cheers, Chris
An expensive day out: Wallet and Grimace

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Using IF/AND in Nested IF

Post by snb »

Seems to be more a question of logic.
And(M2>2;M2>1000) is equal to M2>1000

Code: Select all

=IF(M2>1000;(M2*S8)+(A2*S7)*(1+R9)-100;IF(M2>2;(M2*S8)+(A2*R9);0))

User avatar
BobH
UraniumLounger
Posts: 9218
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Using IF/AND in Nested IF

Post by BobH »

Thank you, one and all!

I'm certain that I was having difficulty county parens and I even found some nonsensical cell references on approaching the formula with fresh eyes. ALL IS SORTED NOW and I get the results I want.

The attached Word document shows how I parsed the formula. If there is a better way to do this, I'd like to learn about it. Obviously, I could have used data names instead of cell references; but this is one month's worksheet with the workbook containing a year. Data names would have to be qualified by worksheet name which creates more problems that are more difficult to resolve (DAMHIKT). :sad:
You do not have the required permissions to view the files attached to this post.
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using IF/AND in Nested IF

Post by HansV »

IF $M2 is > 1000, it is also > 0, so you don't need AND($M2>0,$M2>1000), just $M2>1000 has the same effect.
You use more parentheses than is strictly necessary, but that is not an error.
I'm a bit worried about the following:
In (($M2*$S$7)+($A2*$S$8))*(1+$R$9)-100, you multiply ($M2*$S$7)+($A2*$S$8) with (1+$R$9)
But in ($M2*$S$8)+($A2*$R$9)*(1+$R$9)), you multiply only ($A2*$R$9) with (1+$R$9), not ($M$2*$S$8)
Is that intentional?
Best wishes,
Hans

User avatar
BobH
UraniumLounger
Posts: 9218
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Using IF/AND in Nested IF

Post by BobH »

Thanks for the analysis, Hans!
I do tend to use excessive parentheses but it helps me isolate terms. It also creates problems with I fail to add 1 o fail to delete 1 when needed.

The missing term you pointed out is an oversight in the example. It exists in my spreadsheet. YOU ARE GOOD!

Thanks, again.
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Using IF/AND in Nested IF

Post by ChrisGreaves »

BobH wrote:
28 Jan 2023, 21:15
If there is a better way to do this, I'd like to learn about it.
I believe this to be true: If we can't express the idea in English (or our mother tongue), how can we possibly hope to express it in an unnatural computer language?
I have learned this the hard way, after seeing others hold back on the urge to rush to sit at the keyboard.

By now there are some idiomatic expressions of logic that I can key in straight from my head, but they are rare amongst all the lines of coding over the years.

The bulk of the time I am always better served by sketching then writing my ideas with paper and pencil before trying to key in something in whatever language I have chosen.

I do remember reading an article that said that 90% of our time sitting at a computer is time spent correcting and modifying what we have already keyed in.

Cheers, Chris
An expensive day out: Wallet and Grimace

User avatar
BobH
UraniumLounger
Posts: 9218
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

Re: Using IF/AND in Nested IF

Post by BobH »

I agree Chris, especially with the last statement.

I make of practice of keeping an open Notepad page where I key in the English problem statement. I usually have to edit this to refine and perfect it several times, especially for order of operations. I do this before I try to write the code.

With complex function statements in Excel, I evaluate the formula step by step and check for reasonableness in the interim results. That doesn't always suffice, though. Usually my problems come down to muddled syntax or typos. Once I get caught in that I rarely can see the error without another set of eyes.
Bob's yer Uncle
(1/2)(1+√5)
Intel Core i5, 3570K, 3.40 GHz, 16 GB RAM, ECS Z77 H2-A3 Mobo, Windows 10 >HPE 64-bit, MS Office 2016

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Using IF/AND in Nested IF

Post by ChrisGreaves »

BobH wrote:
30 Jan 2023, 02:22
I make of practice of keeping an open Notepad page where I key in the English problem statement. I usually have to edit this to refine and perfect it several times, especially for order of operations. I do this before I try to write the code.
Hi Bob. I agree (although I use paper because I can take it with me and mull over it while waiting for the dentist!). Best for me is a short statement that the client (nowadays me!) agrees will satisfy him. Like a mini-contract. That paper states my Goal.
With complex function statements in Excel, I evaluate the formula step by step and check for reasonableness in the interim results. That doesn't always suffice, though. Usually my problems come down to muddled syntax or typos. Once I get caught in that I rarely can see the error without another set of eyes.
To me this is the Mechanics of coding. I had some doozies when coding Laidlaw Waste Systems budget. Nowadays I write all the inferior statements (much as we saw in my XLS of a few posts back) and get them working individually, then I copy-and-paste the entire sub-strings to accumulate larger strings, working my way back to the trunk of the tree until the complete statement is assembled and working.

Nowadays even Excel2003 has oodles of space, and we can store those smaller components in a separate worksheet within the same workbook, complete with their self-testing values, for reference twelve months down the road.
I rather wish that spreadsheet processors had two options: For the number of spaces to be inserted preceding and succeeding the parenthesis. That would make reading expressions a little easier, IMHO.

(1) Are you making use of short mnemonic labels for your working cells? (Insert, Name, Define/Create, ...)?
(2) I have not seen that 90% figure verified, or even the study, but I find it alarming. If 90% of our time is spent correcting errors (especially in the field of programing), then it would seem that we are operating at only 10% efficiency. It's as scary as learning that 80% of the energy in gasoline has to be evacuated as waste heat by the automobile engine's cooling system!

Cheers, Chris
An expensive day out: Wallet and Grimace