Formula Help - 3 If statements

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,371
Office Version
  1. 2013
Platform
  1. Windows
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?
 

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
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
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,371
Office Version
  1. 2013
Platform
  1. Windows
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?
 

nniedzielski

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

ADVERTISEMENT

Those are both correct:
E = x or blank
AB equals 0-4

nothing else
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,371
Office Version
  1. 2013
Platform
  1. Windows
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.
 

nniedzielski

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

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
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’)
 
Solution

nniedzielski

Well-known Member
Joined
Jan 8, 2016
Messages
566
Office Version
  1. 2019
Platform
  1. Windows
thanks Fluff, that did work, i marked it as solution and updated my account details for you. sorry about that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,961
Members
422,251
Latest member
bonebreaker100

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
Top