SOLVED

# Multiple If statements

Copper Contributor

# Multiple If statements

What I am trying to do and it is WAY outside my skill set. I can't even begin to type a formula for it.

If B4="kgs" then A8 returns a value of .23

If B4="lbs" then A8 returns a value of .5

If B4="kgs", and D8="Word 1", D8 returns a value of 1, if D8="Word 2", D8 returns a value of 2.  (There are a total of 8 D8 "Word #"s)

If B4="lbs", and D8="Word 1", D8 returns a value of 2, if D8="Word 2", D8 returns a value of 4.  (There are a total of 8 D8 "Word #"s)

So its IF B4= kgs or lbs, A8 = .23 or .5, D8= 1 or 2 or 2 or 4 depending on the D8 "Word #" value.

The end formula for G8 needs to be (A8+((D8+C8)*B8)+F8)

I hope this makes sense.

B4 is a drop down list with "Select" "kgs" "lbs"

D8 is a drop down list with "Word 1" Word 2" all the way up to "Word 8".

Each D8 "Word #" has 2 values depending on B4

8 Replies

# Re: Multiple If statements

D8 cannot contain a text such as "Word 1" and a number such as 2 at the same time...

# Re: Multiple If statements

SWITCH might be the way to go here but first you've got to fix your logic. Start simple by listing your scenarios in one column and desired outcomes in another. Once you've got some coherent logic, a formula can be created. A sample workbook would be great, too.

best response confirmed by Herbal_ingenuity (Copper Contributor)
Solution

# Re: Multiple If statements

Depending on how many variable conditions are involved, this might also call for a two dimensional array, accessed by INDEX and MATCH.

As @Patrick2788 has already said, you could help us help you by articulating all of the conditions and consequences in as orderly fashion as possible (some tabular form would be ideal) and even better, accompany that by posting a copy of the workbook on OneDrive or GoogleDrive with a link here that grants access.

# Re: Multiple If statements

I ended up with this...

=IF(F8="","",IF(A8=0, IF(\$N\$5="kgs", SUM((C8*T8)+E8+F8), SUM((C8*U8)+E8+F8)), IF(\$N\$5="kgs", SUM((C8*T8)+E8+F8+0.23), SUM((C8*U8)+E8+F8+0.5))))

T8 & U8 return values based on on what is in D8.
There is probably something simpler, but I ended up there anyways. I have zero knowledge or background with Excel or computers other than how to surf them. I was able to make this formula through trial and error and google.

# Re: Multiple If statements

I have zero knowledge or background with Excel or computers other than how to surf them. I was able to make this formula through trial and error and google.

One of the best ways to learn Excel and its formulas and functions is through working on a situation you care about and being willing to do some trial and error.  It also helps to do some basic research. YouTube has some great introductory videos. A book like Excel for Dummies can be useful. Websites like ExcelJet also.

# Re: Multiple If statements

@mathetes
That's pretty much what I have been doing. I appreciate the ExcelJet information. I will look into that as there are a few other dilemmas I am working on. I know they are all within the realm of possibilities, I just have to learn how to make them possible.

# Re: Multiple If statements

For the most part, I'm self-taught in Excel (Lotus 1-2-3 before Excel) and got there by reading the manuals. My experience is that the functions generally --not always, just generally--have names that are intuitive, so you can find them by looking for what you'd name the function if you were, say, trying to look things up in a table. And you'll find there's a series of related by different functions that all enable you to LOOKUP things.

# Re: Multiple If statements

Another notation could be

``````=IF(F8="",
"",
E8+F8+ C8*IF(\$N\$6="kgs",T8, U8) +
(0.5 - 0.27*(\$N\$6="kgs"))*(A8<>0) )``````
1 best response

Accepted Solutions
best response confirmed by Herbal_ingenuity (Copper Contributor)
Solution

# Re: Multiple If statements

Depending on how many variable conditions are involved, this might also call for a two dimensional array, accessed by INDEX and MATCH.

As @Patrick2788 has already said, you could help us help you by articulating all of the conditions and consequences in as orderly fashion as possible (some tabular form would be ideal) and even better, accompany that by posting a copy of the workbook on OneDrive or GoogleDrive with a link here that grants access.