Calculate by certain values when you choose YES or NO

[Memar]

I am using the formula below to calculate 10,000 or any number in column A divided by 365 days and multiply by 10 and calculate the result by a number in G1 if value in column B is "YES" or calculate by a number in H1 if the value in column B is "NO". I get the desired result when I have YES value but it gives me zero when I have NO in column B. Would you please amend the formula to get the desired correct result whether the value is "YES" or "NO" in column B.






[TABLE="class: Table TableWordWrap SCXW199349199, width: 0"]
<tbody>[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"] 2 (number in cell G1)

[/TD]
[TD="bgcolor: transparent"]3 (number in cell H1)

[/TD]
[/TR]
</tbody>[/TABLE]







[TABLE="class: Table TableWordWrap SCXW199349199, width: 0"]
<tbody>[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]Column A

[/TD]
[TD="bgcolor: transparent"]Column B

[/TD]
[TD="bgcolor: transparent"]Column C



[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Desired result

Column D

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]10000

[/TD]
[TD="bgcolor: transparent"]YES

[/TD]
[TD="bgcolor: transparent"]=(B1="YES")*A1/365*10*G1+(B1="NO")*A1/365*10*H1

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]548

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]10000

[/TD]
[TD="bgcolor: transparent"]YES

[/TD]
[TD="bgcolor: transparent"]Same Formula [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]548

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]10000

[/TD]
[TD="bgcolor: transparent"]NO

[/TD]
[TD="bgcolor: transparent"]Same Formula [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]822

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR="class: TableRow TableRowSelected"]
[TD="bgcolor: transparent"]10000

[/TD]
[TD="bgcolor: transparent"]NO

[/TD]
[TD="bgcolor: transparent"]Same Formula[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]822

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[drhatmrexcel]

try this in cell C1, but remember if there is a zero value in any of the cells (A1, G1 or H1) that are referenced in the formula then the result is gonna be zero.
=IF(OR(A2="",B2="",G2="",H2=""),"",IF(B2="YES",ROUND(A2/365*10*G2,0),IF(B2="NO",ROUND(A2/365*10*H2,0),"")))


you can preclude a zero value from being entered in cells A1, G1 or H1 by using data validation

 

[jtakw]

Hi,

Also try:

=IF(OR(A2="",B2="",G2="",H2=""),"",A2/365*10*IF(B2="YES",G2,IF(B2="NO",H2,0))
If B2 can be other than YES or NO, results in 0

=IF(OR(A2="",B2="",G2="",H2=""),"",A2/365*10*IF(B2="YES",G2,H2)
If B2 can Only be YES or NO.

Not tested, using my Phone at the moment.

 

[jtakw]

Oh, change G2 and H2 to G$2 and H$2 in my formulas above if you'll be copying the formula down column.

 

[Memar]

try this in cell C1, but remember if there is a zero value in any of the cells (A1, G1 or H1) that are referenced in the formula then the result is gonna be zero.
=IF(OR(A2="",B2="",G2="",H2=""),"",IF(B2="YES",ROUND(A2/365*10*G2,0),IF(B2="NO",ROUND(A2/365*10*H2,0),"")))


you can preclude a zero value from being entered in cells A1, G1 or H1 by using data validation

thanks for the quick response, My actual data has a zero value which can't be precluded, and I also have to include about 30 cells to get the actual calculation. Can we modify the formula?