Need Help Consoldating two IF formulas into 1

atvsource

Board Regular
Joined
Feb 26, 2009
Messages
86
I need help consolidating two IF formulas into one formula.

Here are the two formulas.

F1
Code:
=IF(TablesData!G3=FALSE," ",IF(G$20=1,Analysis!F4,IF(G$20=5,Analysis!J4,IF(G$20=10,Analysis!N4))))
F2
Code:
=IF(TablesData!O7=TRUE," ",Analysis!Q4)
Here's what the two formulas do.
F1 take the TablesData!G3=FALSE and checks to see if that is FALSE, controlled by a form check box, and if FALSE, will make the entire formula not show any results of the IF statements. The G$20=1,5,10 will check to see if those values = 1,5,10 and return the corresponding cells data.

F2 takes the same approch with TablesData!07=TRUE and check to see if that is TRUE, controlled by a form check box, and if TRUE, will make the entire forumla not show any results IF statements. If it returns a false, the cell returns the data in cell Analysis!Q4.

Now, I would like to combine these two into one so if either or of the form check boxes are check, the forumla either show or doesn't show the results.

Can this be done and how would one setup the formula, as I'm stuck.

Here's the formula I've tried without good results, as I cannot get it to toggle to the very last IF.
Code:
=IF(TablesData!G3=FALSE,"",IF(G$20=1,Analysis!F4,IF(G$20=5,Analysis!J4,IF(G$20=10,Analysis!N4,IF(TablesData!O11=TRUE," ",Analysis!Q4)))))
Any suggestions would be appreciated.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
=IF(TablesData!G3=FALSE,IF(TablesData!O11=TRUE," ",Analysis!Q4),IF(G$20=1,Analysis!F4,IF(G$20=5,Analysis!J4,IF(G$20=10,Analysis!N4,))))
 
Upvote 0
With the help of nbrcrunch, I was able to get a little better understanding of combining the two IF statements.

However, I'm still having trouble getting the last part of the formula to show. There are no errors and no VALUE or FALSE values coming back.

Here's the code I'm using:
Code:
=IF(TablesData!G3=FALSE," ",IF(TablesData!O$15=FALSE,"",IF(G$20=1,Analysis!H4,IF(G$20=5,Analysis!L4,IF(G$20=10,Analysis!P4,IF(TablesData!O$15=TRUE,"",IF(G$20=1,Analysis!E4,IF(G$20=5,Analysis!I4,IF(G$20=10,Analysis!M4)))))))))

Basicly, the
Code:
TablesData!G3=FALSE," "
checks to if a check box is checked marked and if so, will show the value, otherwise, the cell returns a blank.

Now, the other two sections of the code work, but only one actually pulls the data into the cell.

This section of the code:
Code:
IF(TablesData!O$15=FALSE,"",IF(G$20=1,Analysis!H4,IF(G$20=5,Analysis!L4,IF(G$20=10,Analysis!P4
works and pulls the data according to the variables.

However, the last part of the code:
Code:
IF(TablesData!O$15=TRUE,"",IF(G$20=1,Analysis!E4,IF(G$20=5,Analysis!I4,IF(G$20=10,Analysis!M4
just returns a blank even though, the conditions have been met.

I'm stumped as to why the first part works, but the later part does not. Can anyone see where the formula is screwy or what might need tweaked to get it to work?
 
Upvote 0
I'm still cannot seem to get this to work... and I"m really stumped here. Any Excel gurus out there that can point me in the right direction?
 
Upvote 0
Personally, it seems to me you are trying to combine two disparate conditions that will never work out. Have you tried working it out on paper to see what happens with each condition? Though I haven't had time to do that, when I was playing with your conditions I got this unsettling feeling that something just wasn't right for the way you were trying to do it.
 
Upvote 0
Ok, I finally dissected it. Here's what I sensed was amiss:

What is the condition of G3 in order for O7 to be examined?

If G3 must be FALSE in order for G7 statement to work, then you need to remove the "Blank" part of the False statement for G3.

If G3 must be TRUE, then in what part of the G20 statement does the O7 statement belong? Should O7 be examined with G20 is 1, 5, or 10 or all?

Do you get what I'm driving at? Your end formula will be much more complicated than just leaving it alone. Further, with the restrictions of embedded IF statements (unless you're using Excel 2007), you may not even be able to make the formula you need in a single cell.

But first you need to work out on paper what conditions work with what and when.

http://www.techonthenet.com/excel/formulas/if_nested.php

.
 
Last edited:
Upvote 0
Ok, I finally dissected it. Here's what I sensed was amiss:

What is the condition of G3 in order for O15 to be examined?

G3 has no condition on what happens with O15, as that is a separate argument for TRUE/FALSE, and is controlled by a form check box. All this does is either show/hide the cell so that it is or not included in a autosum formula later in the column.

If G3 must be TRUE, then in what part of the G20 statement does the O15 statement belong? Should O15 be examined with G20 is 1, 5, or 10 or all?
G20 is a drop-down selection for quantities of 1, 5 or 10. So if G20 shows a certain quantity, then it pulls in the cell reference to the quantity. The IF formulas work when you call them separately, but when you combine them, only the first one will return the value and the second IF formula returns a blank.

NOTE: The IF statements are controlled by a form check box. So if the check box is checkmarked, TRUE IF statemet should be returned and if uncheckmarked, then the FALSE IF statement should be returned.

I've concluded that the TablesData!O$15=TRUE,"", or TablesData!O$15=FALSE,"", "blank" after the TRUE/FALSE is creating the later part of the IF statement to return a blank cell. I've tried removing one or the other blank and get a FALSE value.

Does this make sense now, and any more guidance?
 
Upvote 0
I'm really trying to figure out a way to do this, and I've come up with a different formula, which eliminates several IF statements, and combines them into a INDEX type formula.

However, I'm getting a #VALUE error now and still cannot figure out why:

Here's the new formula being used:
Code:
=IF(TablesData!G3=FALSE,"",
IF(TablesData!O$15=TRUE,"",INDEX(Analysis!E4:M18,MATCH(Analysis!C19,Analysis!C4:C18,0),LOOKUP(G20,{1,5,10},{1,5,9}),
IF(TablesData!O$15=FALSE,"",INDEX(Analysis!H4:P18,MATCH(Analysis!C19,Analysis!C4:C18,0),LOOKUP(G20,{1,5,10},{1,5,9}))))))

If anyone can look at both formulas, the post prior to this and now this post and make suggestions, I would greatly appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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