Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested "If" statements don't always work...

Posted on 2013-02-02
7
Medium Priority
?
318 Views
Last Modified: 2013-02-02
I have four columns. In column BD, cells 9-16 have the following data:
"W1", "W1", "W1", "W2", "W2", "W3", "W3", "W4"
In column BE, cells 9-16 have the following data:
"NR", "R1", "R2", "R3", "R4", "R5", "R6", "R7"

You'll notice that W1 corresponds to NR, R1,& R2, W2 corresponds to R3 & R4, W3 corresponds to R5 & R6, and W4 corresponds to R7.


In column AF, cells 9-30 have the following function assigned:

=INDEX($BD9:$BD16,RANDBETWEEN(1,COUNTA($BD9:$BD16)),1)

Therefore, each of the cells in AF9:AF30 will read any of "W1", "W2", "W3", or "W4" randomly.


Here is my problem: In AV9:AV30 are supposed to analyze AF9:AF30. If, for example, the cell AF9 reads as "W1", then I want it to randomly fill AV9 with any of "NR", "R1", or "R2" (BE9:BE11).

I am currently just trying to get the formula to work, so in cell AV9 my formula currently reads as the following nested "IF" statement:

=IF(AF9="W1",INDEX($BE9:$BE11,RANDBETWEEN(1,COUNTA($BE9:$BE11)),1),+IF(AF9="W2",INDEX($BE12:$BE13,RANDBETWEEN(1,COUNTA($BE12:$BE13)),1))+IF(AF9="W3",INDEX($BE14:$BE15,RANDBETWEEN(1,COUNTA($BE14:$BE15)),1))+IF(AF9="W4","R7","NA"))))

It functions only some of the time, and only at specific times. If AV9="W1", then AV9="NR" or "R1" or "R2" randomly. But if AF9="W2" or "W3" or "W4", it reads as an error. I notice the first part of my nested IF statement refers to W1, so I can see the function only looks at the first part. What must be done to correct this?

I am only focusing on the functions of cells AF9 and AV9 right now, I will modify the final function later.
0
Comment
Question by:AlixTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 16

Assisted Solution

by:kmslogic
kmslogic earned 1000 total points
ID: 38847752
It seems like what you want is more like this (I think you have some paren problems):

Note you have to scrunch this all back together on one line I have it indented to make the paren matches easier to see:

=IF(
	AF9="W1", INDEX($BE9:$BE11, RANDBETWEEN(1,COUNTA($BE9:$BE11)),1),
		IF(AF9="W2",INDEX($BE12:$BE13,RANDBETWEEN(1,COUNTA($BE12:$BE13)),1),
			IF(AF9="W3",INDEX($BE14:$BE15,RANDBETWEEN(1,COUNTA($BE14:$BE15)),1), 
				IF(AF9="W4","R7",
					"NA"
				)
			)
		)
	)

Open in new window

0
 
LVL 26

Accepted Solution

by:
redmondb earned 1000 total points
ID: 38847785
Hi, AlixTech.

The following is a simpler formula...
=IFERROR(INDEX($BE$9:$BE$16,MATCH(AF9,$BD$9:$BD$16,0)+RANDBETWEEN(0,COUNTIF($BD$9:$BD$16,AF9)-1),1),"N/A")

Edit: Changed to handle invalid input. This requires Excel 2007 or higher, so please let me know if you're using an earlier version.

Edit2: This handles earlier versions as well...
=IF(ISERROR(MATCH(AF9,$BD$9:$BD$16,0)),"N/A",(INDEX($BE$9:$BE$16,MATCH(AF9,$BD$9:$BD$16,0)+RANDBETWEEN(0,COUNTIF($BD$9:$BD$16,AF9)-1),1)))

Regards,
Brian.
0
 

Author Comment

by:AlixTech
ID: 38847862
Thanks guys, both of your formulas eliminated the "ERROR". Unfortunately, there's a new problem. "W1", "W2", "W3", and "W4" don't always correspond to ("NR", "R1", "R2"), ("R3", "R4"), ("R5", "R6"), or "R7" respectively. Neither formula acts to account for this even though they seem as if they should. Any thoughts?

I still need:

NR,R1,R2 to randomly be assigned to W1
R3,R4 to randomly be assigned to W2
R5,R6 to randomly be assigned to W3
R7 to randomly be assigned to W4
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 38847890
AlixTech,

In all my testing the allocations were done as you say - if I've understood you correctly. Please see the attached.

Regards,
Brian.Random-Allocation.xls
0
 

Author Comment

by:AlixTech
ID: 38847907
Ah, I see. Thanks so much! I accidentally assigned the formula without appropriately modifying it for each cell in list AV9:AV30. Both formulas work perfectly, and now I can design just one formula if I wish. Thanks!!
0
 

Author Closing Comment

by:AlixTech
ID: 38847908
Easy to understand, even for a novice like myself. Both solutions were exactly as I needed, the second was a simpler way to do it that I didn't know before, so now I know two ways to accomplish similar tasks.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38847924
Thanks, AlixTech.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question