IF and/or statement, true/false results different

[janesdtr]

Hello everyone,

I am having trouble constructing a formula that looks for either of 2 conditions and then returns different true and false values, depending on the conditions. I can successfully construct the formula when the true or false condition is the same for the 2 conditions, but can't wrap my head around this more complicated scenario.

Here I go:

If a2>0 AND c2=yes, then return a2*2 (if true) or a2 (if false)
OR
if b2>0 AND c2=yes, then return b2*2 (if true) or b2 (if false)

Thank you in advance for any advice you scoot my way!

Mary Beth

 

[martindwilson]

although both could be true you cant have 2 different results in the same cell
which value would you want? a2*2 or b2*2

 

[Scott Huish]

What result do you expect in this scenario and why?

Excel Workbook
	A	B	C
1
2	2	4	yes
3
Sheet1

 

[janesdtr]

Sorry for not giving context. This is used to track employee mileage expense.

We have mileages that are standard/pre-determined and we have mileages that employees compute from their odometers.

A2 (through a1000) is a VLookup that populates with a list of our standard mileages based on the location the employee chooses.
B2 (through b1000) is for computed mileages.
C2 (through c1000) asks whether it was a round trip.
D2 is the total mileage for the trip.

Formula in D2: If C2 says yes, then the formula in D2 doubles either a2 or b2. If C2 says no, D2 simply displays the value in a2 or b2.

Please let me know if you need more information. And...

THANKS!

 

[janesdtr]

I can get this far, but this assumes the true and false answers are the same. Sadly, they're not.

=IF(OR(AND(A2>0,C2="yes"),AND(B2>0,C2="yes")),A2*2,A2)

 

[Scott Huish]

Will there only be a value in A2 or B2. If not, how is it supposed to pick which of the 2 values to use?

 

[janesdtr]

Will there only be a value in A2 or B2. If not, how is it supposed to pick which of the 2 values to use?

Yes, an employee would have a value in EITHER A2 or B2, but not both.

Let me know if you need more information - grazie.

 

[Scott Huish]

=(A2+B2)*IF(C2="yes",2,1)

 

[villy]

I analyzed your query like this: Either [A2] or [B2] have the value but not both or supposed both have the value other than 0 then it will priorities the value in [A2].

[D2]=IF(C2="yes",IF(A2<>0,A2,B2)*2,IF(A2<>0,A2,B2))

Open for comments.
Thanks

 

[Scott Huish]

Only 1 of the 2 cells will have a value so you can add them together and get the 1 value. Then just check Column C for yes to see if you need to double that or not.

Excel Workbook
	A	B	C	D
2	7			7
3		9		9
4		10	yes	20
5	10		yes	20
Sheet1