Math Calculation Problem

[muhammad susanto]

hi all...

how to make this formula to calculate 2 conditions :
this formula :

	C	D	E	F	G
7		data entry / given
8		TP	TR	TN
9		1995	2006	2017
10
11
12		after formula
13	with "TR"	15
14	without "TR"	?
15
16		if I delete "TR" the result is false shoul be 2017-1995=22

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=E0E0F0"" target="_blank"></colgroup><colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D13 =ROUND(((F9-D9)+2*(F9-E9))/3,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

any help much appreciated..
m.susanto<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid <a href="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC" target="_blank">#CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid <a href="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCC" target="_blank">#CCC ;
}
.tableizer-table th {
background-color: <a href="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=104E8B" target="_blank">#104E8B ;
color: <a href="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF" target="_blank">#FFF ;
font-weight: bold;
}
</style>

 

[muhammad susanto]

	data entry / given
	TP	TR	TN
	1995	2006	2017
	after formula
with "TR"	15
without "TR"	?
	if I delete "TR" the result is false shoul be 2017-1995=22

<tbody>
</tbody>

 

[Joe4]

I don't understand your conditions and the impact they should have on your formula.

Can you just explain what your two conditions are, and what it should do in each case?

 

[philwojo]

If you remove the value in TR, 2006 in your example, you would have 2017-0 for the 2nd part of your equation (F9-E9), so that would result in an answer, just for that part of 2017.

F9-D9 = 2017-1995 which is 22.

So you would have 2017*2 or 4034 + 22 = 4056/3 = 1352, so that is the result I would expect.

I think you need to us an IF statement maybe this:

=IF(E9="",ROUND(((F9-D9)),0),ROUND(((F9-D9)+2*(F9-E9))/3,0))

Phil

 

[muhammad susanto]

i have 2 formula in different cell :

(formula 1) = UE=TN-TP
(formula 2) = UE=(TN-TP)+2 (TN-TR)/3

i have using formula =ROUND(((F9-D9+(E9<>0)*2*(F9-E9))/3);0). It formula works in only condition 2 (formula 2) but not work in formula 1
i want to how to make 1 formula for working in 2 condition above...

please, i hope someone help me out...

 

[muhammad susanto]

If you remove the value in TR, 2006 in your example, you would have 2017-0 for the 2nd part of your equation (F9-E9), so that would result in an answer, just for that part of 2017.

F9-D9 = 2017-1995 which is 22.

So you would have 2017*2 or 4034 + 22 = 4056/3 = 1352, so that is the result I would expect.

I think you need to us an IF statement maybe this:

=IF(E9="",ROUND(((F9-D9)),0),ROUND(((F9-D9)+2*(F9-E9))/3,0))

Phil

thank Phil, worked great!!!

thanks a lot...