Jul 20 2023 01:58 PM
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
Jul 20 2023 02:15 PM
D8 cannot contain a text such as "Word 1" and a number such as 2 at the same time...
Jul 20 2023 03:28 PM - edited Jul 20 2023 03:29 PM
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.
Jul 20 2023 06:05 PM
Solution
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.
Dec 26 2023 12:15 PM
Dec 26 2023 02:25 PM
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.
Dec 26 2023 02:30 PM
Dec 26 2023 02:38 PM
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.
Dec 27 2023 06:43 AM
Another notation could be
=IF(F8="",
"",
E8+F8+ C8*IF($N$6="kgs",T8, U8) +
(0.5 - 0.27*($N$6="kgs"))*(A8<>0) )
Jul 20 2023 06:05 PM
Solution
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.