Formula Help - 3 If statements

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
590
Office Version
  1. 2019
Platform
  1. Windows
hi guys,

I am struggling to get my formula to work correctly, I have 3 If statements i am trying to layer properly and cant seem to get the formula to work right.

I have a list of data in a separate sheet with values. Values are between 0-4.

First thing: If no value in U1, then the cell returns a blank
Second thing: If all values in Training data are 4's, then the cell returns a 2
Third thing: If all values are 2 or greater (but not all four), then the cell returns a 1

This is the closest I have gotten, but it still says there is an error in the formula

VBA Code:
=IF(U$1=0,"",If(countifs('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,"4")"2")IF(COUNTIFS('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,"<2")>0,"0","1"))

What is missing from getting this thing to connect correctly?

as always, thank you so much for any help
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi,
Your formula is missing a couple of commas in order to return a value.


VBA Code:
=IF(U$1=0,"",IF(COUNTIFS('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,"4"),"2",IF(COUNTIFS('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,"<2")>0,"0","1")))
However, I don't think that will give the value you are wanting?
You are counting the entries in Training Data that comply but you are not comparing them to anything. Thus just one x & 4 will return 2, etc.
Do you wish all rows 3 to 217 to contain eg 4 to return a 2 or just that all rows that have a value be 4 in order to return 2, etc?
 
Upvote 0
Thank you for your time and questions Snakehips, let me see if i can answer to khelp dieal you into my issue.

I do want to compare Training Data in E to Training Data in AB, so if they are not comparing now, how do i connect them?

So if all the values of "x" in E, are rated a 4 in AB, then this cell returns a 2, and if all the values of "x" in E, are rated greater than 2 in AB, then this cell returns a 1, and then if neither of those apply, it returns a 0.

Does this help you with what i am looking for?

Again, thank you
 
Upvote 0
Yes, that helps.
One more question before I try and formulate an answer.
Will there be any other values in E, other than x or blank and 0 to 4 or blank in AB?
 
Upvote 0
Try.....

VBA Code:
=IF(U$1=0,"",IF(COUNTIF('Training Data'!$E$3:$E$217,"x")=COUNTIF('Training Data'!AB$3:AB$217,"4"),"2",IF(COUNTIF('Training Data'!$E$3:$E$217,"x")=COUNTIF('Training Data'!AB$3:AB$217,">=2"),"1","0")))
Not sure if you want that final option as "0" or "" ? Edit as you see fit.

Hope that helps.
 
Upvote 0
I ran a test with every cell in AB with 4's, 3's, 2's and 1's, no matter what it returned as a "0" nothing i did could get it to do anything different. So i am not sure exactly what is wrong, not getting an error message, just getting a returned 0 no matter what
 
Upvote 0
Maybe
Excel Formula:
=IF(U$1=0,"",IF(COUNTIFS('Training Data'!$E$3:$E$217,"x")=COUNTIFS('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,4),2,IF(COUNTIFS('Training Data'!$E$3:$E$217,"x")=COUNTIFS('Training Data'!$E$3:$E$217,"x",'Training Data'!AB$3:AB$217,">2"),1,0)))

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Solution
thanks Fluff, that did work, i marked it as solution and updated my account details for you. sorry about that.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
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