Simplifying Multiple AND, IF & OR Function

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Out of boredom I am trying to keep my mind active by finding solutions to challenges using Excel. My current challenge requires a specific cell (Cell C) to be populated with a value based on certain combinations from multiple sources (X, Y & Z)

When the combination includes 1 value from Y and 1 value from Z then IF, AND and OR functionality is workable, if not intricate.

I am hoping that there might be another option available in Excel that will provide a solution for multiple possible combinations from multiple sources. TRUE / FALSE is acceptable as Source Z may have a different value which should provide a FALSE return (ZERO)

I would also be interested if the use of a macro with VBA code might be a possibility.

AIM = Cell C to be populated with the value of Cell A OR the value of Cell B OR the value of Cell A + Cell B depending on the combination determined by the combinations of Sources Y & Z (See combinations Below)

Source X = two cells (Cell A & Cell B) each with values populated from a database.

Source Y (Cell 1) has two possible values (eg F & G) one one of which can be applied to each calculation. Thisi is readily determined using IF & AND functionality as only one value can be applied to the calculation.

Source Z however uses combinations of up to 3 additional cells (eg. A5. A6 & A7). Each cell has 2 possible valid values (eg W & E) giving 8 possible unique combinations (eg WWW, WWE, WEE etc.). The formula needs to include all possible combinations of Source Z for each Value of Source Z. So there are possibly 16 different combinations

Cell C = Value Cell A if Options are (Source Y Value = F Acceptable Source Z Cell A5, A6 & A7 combinations WWW, WWE, WEW & EWW {eg. A5 ="W",A6="W",A7="W" etc.})
Cell C = Value Cell B if Options are (Source Y Value = G Acceptable Source Z Cell A5, A6 & A7 combinations WEE, EEE, EEW & EWE)
Cell C = Value Cells A+B if Options are (Source Y Value = G Acceptable Source Z Cell A5, A6 & A7 combinations WWW, WWE, WEW, WEE, EWW, EEE, EEW & EWE).

Any help would be greatfuly accepted
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please tell what is formula for Y also. AND
Your data start from row ? AND
Please upload Example file And Desired Result with 10-20 rows with XL2BB ADDIN or upload at free hosting site e.g. www.dropbox.com or OneDrive or GoogleDrive and Insert Link here.
 

Denny57

New Member
Joined
Nov 23, 2015
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Maabadi.. Thank you for taking the time to look at this. Since I posted I have tried a number o different options and have just realised that I will need to expand the possible combinations and had omitted a possible option so the level that formulae will be impracticle. I am now invetigating using VBA.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,687
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Tell your conditions with example file. maybe can help with VBA also.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,161
Messages
5,629,050
Members
416,363
Latest member
zaveedd

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