Excel 2003 complicated formula

[pdeman]

I have the following data with this formula =IF(D1=0,"",(B1/100*D1)*(1-E1%)) in column F.
In this portion of the expression ...(B1/100*D1)... I need D1 to increment over the twelve months of a year and not just *D1?

So in the following example with the formula above the total in column F is only right in December on each row. This of course is the problem and why I need to multiply it incrementally for each month and not just the whole D1 value.

Column D represents a full years percent value for each row for the year in column A.
So for row 1 the formula in Column F must in January compute 1 twelfth of D (3.75) in February 2 twelfths and so on. The date for row 1 would be the 12 months of the year the year for row 1 is 2001.

Column D figure needs to stay as it is so must all be done in Column F formula.
Sorry for the long explanation but it's not easy to explain.

A B C D E F
1 2001 10,000 3.50 3.75 20.00 £300.00
2 2002 15,000 2.00 3.50 20.00 £420.00
3 2003 15,000 1.67 3.50 20.00 £420.00
4 2004 15,000 2.33 3.25 20.00 £390.00
5 2005 15,000 3.33 3.25 20.00 £390.00

 

[SkipVought]

Hi,

Where is the date, in order to determine the month?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Hi Skip,

The month has to be computed on the fly probably with some clever use of the Today function or something similar.

The month will be whatever the current month is.

So if column A is 2010 the month would be February and the formula in column F would multiply the percent in column D based on 2 twelfths of 3.25.

 

[SkipVought]

The month will be whatever the current month is.

[tt]
=IF(D1=0,"",(B1/100*D1*month(Today())/12)*(1-E1%))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Hi Skip,

1. Can I substitute the Today() part with December or 12 for example to see the figure the expression produces just for testing purposes.

2. What do I add to the front of the expression to have the expression caculate as before ie the full 12 months for any previous year in column A. ie pre 2010

 

[SkipVought]

I have no idea what either of your points is trying to convey.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

1. Your columns need HEADINGS. Structure your data as proper TABLES.

2. Your table seems to be inadequate, as there is no DATE with which to relate to your formula. As I originally asked, "Where is the date, in order to determine the month?"

Bottom line: Rework your workbook design, IMNSHO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

1. yes, if you hard code it rather than using the today function

2. try:-
if(a1=year(today()),month(today())/12,1)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[pdeman]

OK guys, I will work it out, thanks anyway

 

[SkipVought]

Please post your solution for the benefit of all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

pdeman:

I hope you're not getting defensive. You just haven't given us enough information to help you.

I think I understand most of what you want, but the single most critical piece of information is missing from your sample data.... What would you want to return for this year. Since the part of the formula you need help with only kicks in if the year in Column A is the current year, that's the sample we really need to see to make sure we understand your requirements.

In the mean time, give this a shot:
[tab][COLOR=blue white]=IF(D5 = 0, "", IF(YEAR(TODAY()) > A5, (B5 / 100 * D5) * (1 - E5%), (B5 / 100 * D5 * MONTH(TODAY()) / 12) * (1 - E5%)))[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[pdeman]

anotherhiggins

Not getting at all defensive just trying to work out something that to me is complicated which is probably why my information seems a little confusing doing the best I can though.

If the answer wasn't obvious to anyone I didn't want to cause anyone any further bother.

Having said that you seemed to have grasped exactly what I was getting at. Your code does kind of work it returns the right data for the current year but all previous years should represent the full 12 months unfortunately it calculates as 1 twelfth for all years although logically it seems correct in practice it seems to do something else?

Oddly enough I too came up with more or less the same string as you have and it also behaves in exactly the same way as yours?

 

[SkipVought]

Here's my shot...
[tt]
=IF(D1=0,"",(B1/100*D1*month(Today())/if(year(today())=a1,12,month(Today())))*(1-E1%))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pdeman]

Hi anotherhiggins, Skip

Both strings work. I had some strange formatting going on in column A which was affecting the date changed that and now both expressions work.

Thanks