Forum Discussion
Solved
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
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.
8 Replies
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.
- 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.Another notation could be
=IF(F8="", "", E8+F8+ C8*IF($N$6="kgs",T8, U8) + (0.5 - 0.27*($N$6="kgs"))*(A8<>0) )
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.
D8 cannot contain a text such as "Word 1" and a number such as 2 at the same time...
