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

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
HIS


"Joe Black" wrote in message
...
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

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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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

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:

=(J5F5)*((E5I5+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


"Joe Black" wrote in message
...
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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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

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


"Sandy Mann" wrote:

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:

=(J5F5)*((E5I5+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


"Joe Black" wrote in message
...
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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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 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

"Sandy Mann" wrote:

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:

=(J5F5)*((E5I5+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


"Joe Black" wrote in message
...
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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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

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

=(J5F5)*((J5-F5)/MOD(14,4+(E5-I5365)))
or
=(J5F5)*((J5-F5)/(2*(E5-I5365)+2))

--
Dana DeLouis
Win XP & Office 2003


"Sandy Mann" wrote in message
...
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:

=(J5F5)*((E5I5+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


"Joe Black" wrote in message
...
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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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

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


"Joe Black" wrote in message
...
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

"Sandy Mann" wrote:

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:

=(J5F5)*((E5I5+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


"Joe Black" wrote in message
...
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


"Nick Hodge" wrote:

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
HIS


"Joe Black" wrote in message
...
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