Change how total is calulated based on contents of cells

[MikeyMo]

Hard to title this post, but the issue is as follows:

I have three cells (A1,B1,C1) with scores and a fourth (D1) with the totals.
Cell D1 contains the formula: =(A1* .25) + (B1* .25) + (C1* .5). Simple enough.

However, the requirement is if either A1 or B1 does not contain a score, then the value in A1 or B1 needs to be multiplied by .50 instead of .25. For example, if A1 is null then B1*.50.
Finally, if A1 and B1 are both null, the total will only be calculated using C1, which will always have a score.

I have attached an image of this for reference. Looking for the cleanest way to do this with formulas. VBA is not my strong game but I'd be willing to thrash through it if it works better.

Thanks Folks.
Mikey

 

[CA_Punit]

Is this what you want. I have a confusion.
If both A1 and B1 are Null then what will be the value. I have calculated as C2

Book1
	A	B	C	D
1	Score One	Score Two	Score Three	Total
2	70	90	80	40
3
Sheet2

Cell Formulas
Range	Formula
D2	D2	=IF(AND(B1="",A1=""),C2,A2*IF(B1<>"",0.25,0.5)+B2*IF(A1<>"",0.25,0.5))

 

[Fluff]

Another option
=SUM(A2,B2)*IF(OR(A2="",B2=""),0.5,0.25)+C2*0.5

 

[MikeyMo]

CA, to answer your question...

If A2 and B2 are null, then the calculation will just be C2*.50. C2 is the only constant in the formula. The validation is for A2 and B2.

If A2=Null, then B2*.50
If B2=Null, then A2*.50
Otherwise, if both A2 and B2 have values the formula will remain the same. Basically this - =(A2*0.25)+(B2*0.25)+(C2*0.5)

 

[CA_Punit]

=IF(AND(B1="",A1=""),C2*.50,A2*IF(B1<>"",0.25,0.5)+B2*IF(A1<>"",0.25,0.5))

 

[MikeyMo]

Another option
=SUM(A2,B2)*IF(OR(A2="",B2=""),0.5,0.25)+C2*0.5

Fluff.

This is working. I can't make sense of it but it seems to work. =) I need to dig into it and formulate some english around it. =))

 

[Fluff]

It's basically saying that if either A2 or B2 is "" then multiply the sum of A2 & B2 by 0.5, otherwise multiply the sum by 0.25.
If both A2 & B2 are "" then it doesn't matter what you multiply the sum by as it will come to 0.

 

[CA_Punit]

I am messed up. your query says B1 .. But @Fluff response has no reference to B1 A1...

 

[MikeyMo]

I am messed up. your query says B1 .. But @Fluff response has no reference to B1 A1...

That was my mistake. My image is correct, but I referenced row one by mistake in my explanation.

Thanks to both of you for the quick turnaround.

 

[CA_Punit]

I think my solution is not correct. Please ignore and pardon me.