If-Then statement problem

[Allison]

Hello!

I'm working on a Claim speadsheet that needs an If-Then
statement, or at least I think that's what it needs, and
I'm not having any luck. Here's the set up:

I have three columns that, when totaled, will give me a
Value of Claimed Loss amount, =(G14*(1-H14))-I14.
However... if these three cells aren't applicable to the
claim, they will remain at 0 and just the Repair Cost
(F14) will apply and that amount it supposed to show in
the Value of Claimed Loss column (J14).

So, esentially, it breaks down to: =(G14*(1-H14))-I14
should = total in J14, but if G14, H14 and I14 are 0, then
F14 should carryover to J14.

Several hours of trying have not yielded a working
calculation and it could be because I'm not in the right
calculation ballpark, If that's the case, any help or
directions to the right calculation would be greatly
appreciated.

Thanks!
Allison

 

[JulieD]

Hi Allison

=if(and(G14=0,H14=0,I14=0),F14,G14*(1-H14))-I14 )
if you want all three to be 0 to get f14 in the cell rather than the calc

or
=if(or(G14=0,H14=0,I14=0),F14,G14*(1-H14))-I14 )
if you want f14 in the cell if any of G14/ H14 or I14 are = 0


let us know how you go

Cheers
JulieD

 

[Bob Phillips]

Allison,

Try this in J14

=IF(SUM(G14,H14,I14)=0,F14,=(G14*(1-H14))-I14 )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi Bob

think you meant

=IF(SUM(G14,H14,I14)=0,F14,(G14*(1-H14))-I14 )

Cheers
JulieD

 

[Bob Phillips]

Damn copy and paste, it always decides how much to copy.

Thanks Julie.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)