Formula

[Guest]

i am trying to work out a formula to:
calculate a cell but only if another cell is of greater value than
another,(if not then value in cell = 0) then if the value is greater and the
date is under 365 days calculates at %50 rate and if its over, calculate at
%25 rate. can i do this from one cell/one formula

 

[Nick Hodge]

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we get the
date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

thanks for you help Nick. here are the cells i hope this clears it more. any
further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5 $0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate. now
it only needs to be calculated if the value of j5 is greater than f5. if not
then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of j5-f5, if
its greater than 365 then i need %25 of the sum of j5-f5. This might sound
like the long way but i don't know another for now.( until you show me ). i
hope ive explained it right and enough info. cheers

 

[Sandy Mann]

Joe,

There is nothing wrong with Nick's suggestion - in fact it is the
*conventional* way to solve the problem. I sometimes use Boolean arithmetic
instead, which does the same thing but is a lot less intuitive:

=(J5>F5)*((E5>I5+365)/4+(E5<=I5+365)/2)*(J5-F5)

In Excel TRUE/FALSE Booleans are converted to 1/0 when used in maths, so the
above evaluates to:

(TRUE)*((FALSE)/4+(TRUE)/2)*(4500-4000)

which in turn evaluates to:

(1)*((0/4)+(1)/2)*(500)

thus:

0.5*500


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

thanks for you help Nick. here are the cells i hope this clears it more. any
further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5 $0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate. now
it only needs to be calculated if the value of j5 is greater than f5. if not
then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of j5-f5, if
its greater than 365 then i need %25 of the sum of j5-f5. This might sound
like the long way but i don't know another for now.( until you show me ). i
hope ive explained it right and enough info. cheers

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we get the
date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Thanks sandy. everything worked except the date part.. its not calculating
the difference between 1 year and under @ %50 and over 1 year @ %25

Joe,

There is nothing wrong with Nick's suggestion - in fact it is the
*conventional* way to solve the problem. I sometimes use Boolean arithmetic
instead, which does the same thing but is a lot less intuitive:

=(J5>F5)*((E5>I5+365)/4+(E5<=I5+365)/2)*(J5-F5)

In Excel TRUE/FALSE Booleans are converted to 1/0 when used in maths, so the
above evaluates to:

(TRUE)*((FALSE)/4+(TRUE)/2)*(4500-4000)

which in turn evaluates to:

(1)*((0/4)+(1)/2)*(500)

thus:

0.5*500


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

thanks for you help Nick. here are the cells i hope this clears it more. any
further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5 $0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate. now
it only needs to be calculated if the value of j5 is greater than f5. if not
then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of j5-f5, if
its greater than 365 then i need %25 of the sum of j5-f5. This might sound
like the long way but i don't know another for now.( until you show me ). i
hope ive explained it right and enough info. cheers

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we get the
date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


i am trying to work out a formula to:
calculate a cell but only if another cell is of greater value than
another,(if not then value in cell = 0) then if the value is greater and
the
date is under 365 days calculates at %50 rate and if its over, calculate
at
%25 rate. can i do this from one cell/one formula

 

[Guest]

Sandy disregard the previous message. i tweeked around with the formula and
now all is working perfect. i don't understand %100 how it works, but i will
study it a bit more and might work it out eventually.
thankyou both again. nick and sandy

Joe,

There is nothing wrong with Nick's suggestion - in fact it is the
*conventional* way to solve the problem. I sometimes use Boolean arithmetic
instead, which does the same thing but is a lot less intuitive:

=(J5>F5)*((E5>I5+365)/4+(E5<=I5+365)/2)*(J5-F5)

In Excel TRUE/FALSE Booleans are converted to 1/0 when used in maths, so the
above evaluates to:

(TRUE)*((FALSE)/4+(TRUE)/2)*(4500-4000)

which in turn evaluates to:

(1)*((0/4)+(1)/2)*(500)

thus:

0.5*500


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

thanks for you help Nick. here are the cells i hope this clears it more. any
further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5 $0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate. now
it only needs to be calculated if the value of j5 is greater than f5. if not
then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of j5-f5, if
its greater than 365 then i need %25 of the sum of j5-f5. This might sound
like the long way but i don't know another for now.( until you show me ). i
hope ive explained it right and enough info. cheers

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we get the
date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


i am trying to work out a formula to:
calculate a cell but only if another cell is of greater value than
another,(if not then value in cell = 0) then if the value is greater and
the
date is under 365 days calculates at %50 rate and if its over, calculate
at
%25 rate. can i do this from one cell/one formula

 

[Dana DeLouis]

Here are two additional ideas along this excellent technique...

=(J5>F5)*((J5-F5)/MOD(14,4+(E5-I5>365)))
or
=(J5>F5)*((J5-F5)/(2*(E5-I5>365)+2))

--
Dana DeLouis
Win XP & Office 2003

Joe,

There is nothing wrong with Nick's suggestion - in fact it is the
*conventional* way to solve the problem. I sometimes use Boolean
arithmetic
instead, which does the same thing but is a lot less intuitive:

=(J5>F5)*((E5>I5+365)/4+(E5<=I5+365)/2)*(J5-F5)

In Excel TRUE/FALSE Booleans are converted to 1/0 when used in maths, so
the
above evaluates to:

(TRUE)*((FALSE)/4+(TRUE)/2)*(4500-4000)

which in turn evaluates to:

(1)*((0/4)+(1)/2)*(500)

thus:

0.5*500


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

thanks for you help Nick. here are the cells i hope this clears it more. any
further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5
$0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate.
now
it only needs to be calculated if the value of j5 is greater than f5. if not
then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If
i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of j5-f5, if
its greater than 365 then i need %25 of the sum of j5-f5. This might
sound
like the long way but i don't know another for now.( until you show me ). i
hope ive explained it right and enough info. cheers

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we get the
date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


i am trying to work out a formula to:
calculate a cell but only if another cell is of greater value than
another,(if not then value in cell = 0) then if the value is greater and
the
date is under 365 days calculates at %50 rate and if its over, calculate
at
%25 rate. can i do this from one cell/one formula

 

[Sandy Mann]

Hi Joe,

Glad that you got it working- that is the important thing. I seemed to have
the I's & E's mixed up but I think that I may have been mislead by your
saying that K5 should have 250. With the dates that you gave surely it
should have 125?

I like Dana's formulas - esoteric as ever - but I/you seem to have mislead
him in the same way as well.

Regards


Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

Sandy disregard the previous message. i tweeked around with the formula and
now all is working perfect. i don't understand %100 how it works, but i will
study it a bit more and might work it out eventually.
thankyou both again. nick and sandy

Joe,

There is nothing wrong with Nick's suggestion - in fact it is the
*conventional* way to solve the problem. I sometimes use Boolean arithmetic
instead, which does the same thing but is a lot less intuitive:

=(J5>F5)*((E5>I5+365)/4+(E5<=I5+365)/2)*(J5-F5)

In Excel TRUE/FALSE Booleans are converted to 1/0 when used in maths, so the
above evaluates to:

(TRUE)*((FALSE)/4+(TRUE)/2)*(4500-4000)

which in turn evaluates to:

(1)*((0/4)+(1)/2)*(500)

thus:

0.5*500


HTH

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

thanks for you help Nick. here are the cells i hope this clears it

more.
any

further q's please ask.

[B5 13,559][C5 $0.295][D5 $29.950][E5 15-Nov-04][ F5 $4,000.00][G5 $0.315]
[H5 $29.950] [I5 19-Nov-04] [J5 $4,500.00] [K5 $250.00] [L5 $190.100]

I'LL try explain this. lol. k5($250) is the value i need to calculate. now
it only needs to be calculated if the value of j5 is greater than f5.

if
not

then k5=0.
now if j5 is greater than f5 there are 2 calulcations to be made, If i5-e5
is less than or equal to 365(1year) then i need %50 of the sum of

j5-f5,
if

its greater than 365 then i need %25 of the sum of j5-f5. This might sound
like the long way but i don't know another for now.( until you show

me ).
i

hope ive explained it right and enough info. cheers


:

Jo

Certainly, check out the IF function and you can nest these like so

=IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False))

This is only set out to show the approx syntax. If you want an exact
formula then try and let us now the cells you are using, where we

get
the

date from, etc (A little more clearly)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)


i am trying to work out a formula to:
calculate a cell but only if another cell is of greater value than
another,(if not then value in cell = 0) then if the value is

greater
and

the
date is under 365 days calculates at %50 rate and if its over, calculate
at
%25 rate. can i do this from one cell/one formula