J
Jason T
Hi, I was wondering if anyone had any ideas on which function(s) would be
best to use to calculate the following
If I had a table, for example:
A1 B1 C1 D1
Purchase Date Cost Balance Anniversary Date
24/09/96 $100.00 $300.00 D2
28/10/98 $100.00 C3 D3
02/11/99 $100.00 C4 D4
12/12/00 $100.00 C5 D5
10/11/01 $100.00 C6 D6
22/11/02 $100.00 C7 D7
and wanted a function(s) that could calculate the following, which
function/arguments would be best to use -
On 24/9/96 I receive a $400 subsidy & make a purchase of $100, balance =
$300. Each year after that, for 2 years (on 24/9/97 & 24/9/98) I receive
another $180 for each year, which I can carry over for a maximum of 3 years
from 24/9/96 (so any available credit would expire on 24/9/99, if not
spent). I need a function in Cell C3 that checks the corresponding purchase
date (28/10/98) & if it is within 12 months of A2 (24/9/96) deducts the cost
(B3 $100) from the balance (C2 $300), if it is between 12 & 24 months of A2
24/9/96 adds $180 and then deducts the cost (B3 $100) from the balance (C2
$300), if it is between 24 & 36 months of A2 24/9/96 adds $360 and then
deducts the cost (B3 $100) from the balance (C2 $300) & if it is 36 months
or more than A2 24/9/96 (24/9/99+) cancels out any credit carried over from
the previous 3 year cycle, adds $400 & deducts (B3 $100). Cell C5 would need
to check that the purchase date A5 12/12/00 is either within 12 months,
between 12-24 or more than 36 months than Cell A4 2/11/99 & then carry out
the above calculations also, and so on.... I've tried nested variations of
the functions IF, AND, OR but can't seem to get what I need. Any suggestions
would be much appreciated.
Thanks.
best to use to calculate the following
If I had a table, for example:
A1 B1 C1 D1
Purchase Date Cost Balance Anniversary Date
24/09/96 $100.00 $300.00 D2
28/10/98 $100.00 C3 D3
02/11/99 $100.00 C4 D4
12/12/00 $100.00 C5 D5
10/11/01 $100.00 C6 D6
22/11/02 $100.00 C7 D7
and wanted a function(s) that could calculate the following, which
function/arguments would be best to use -
On 24/9/96 I receive a $400 subsidy & make a purchase of $100, balance =
$300. Each year after that, for 2 years (on 24/9/97 & 24/9/98) I receive
another $180 for each year, which I can carry over for a maximum of 3 years
from 24/9/96 (so any available credit would expire on 24/9/99, if not
spent). I need a function in Cell C3 that checks the corresponding purchase
date (28/10/98) & if it is within 12 months of A2 (24/9/96) deducts the cost
(B3 $100) from the balance (C2 $300), if it is between 12 & 24 months of A2
24/9/96 adds $180 and then deducts the cost (B3 $100) from the balance (C2
$300), if it is between 24 & 36 months of A2 24/9/96 adds $360 and then
deducts the cost (B3 $100) from the balance (C2 $300) & if it is 36 months
or more than A2 24/9/96 (24/9/99+) cancels out any credit carried over from
the previous 3 year cycle, adds $400 & deducts (B3 $100). Cell C5 would need
to check that the purchase date A5 12/12/00 is either within 12 months,
between 12-24 or more than 36 months than Cell A4 2/11/99 & then carry out
the above calculations also, and so on.... I've tried nested variations of
the functions IF, AND, OR but can't seem to get what I need. Any suggestions
would be much appreciated.
Thanks.