15 replies to this topic

[Ethel]

Anyone any good with formulas

I want to make a spread sheet that will look at a list and select all the values* within a year of the current date (or it could look the date up in a cell); add them up and work out a weekly average value.

Can't be that hard can it - any pointers?

* it only needs 2 columns one for dates and one for currency values.

[Millers Mini]

Anyone any good with formulas

I want to make a spread sheet that will look at a list and select all the values* within a year of the current date (or it could look the date up in a cell); add them up and work out a weekly average value.

Can't be that hard can it - any pointers?

* it only needs 2 columns one for dates and one for currency values.

Have you tried the IF command?

[mad_scientist]

Anyone any good with formulas

I want to make a spread sheet that will look at a list and select all the values* within a year of the current date (or it could look the date up in a cell); add them up and work out a weekly average value.

Can't be that hard can it - any pointers?

* it only needs 2 columns one for dates and one for currency values.

cant remember how to do averages yet, but just to add them up just highlight them all then another square underneath them then go to the figure that looks like a "z" and press and it should give answer if i remember rightly .could be wrong though

[Ethel]

Cheers folks,

I can just about manage the autosum bit and figured I'd need to get out me booleans (ooer). It's specifying the year range I expect to struggle with.

Toying with using Access so I can use a query but sure that'd be a long winded way of doing it.

Getting it all into 1 formula would be nice.

[Matt]

tell ya what..

you do an excel file with the data you want on it.. and send it to me.. and ill have a go
doesnt matter if its on 2 sheets.

[Millers Mini]

I have done a sheet how do I upload it?

[Jupitus]

If dates are in column A, and values are in column B, then the following formula cut n pasted in C helps:

=IF(TODAY()-A1<365,B1,0)

You can then autosum column C.

[Jammy]

If dates are in column A, and values are in column B, then the following formula cut n pasted in C helps:

=IF(TODAY()-A1<365,B1,0)

You can then autosum column C.

I believe that would sum the values of those in the past 365 days, which isn't quite the same as only selecting values in 2007, for example. Depends what Ethel wants to do.

Ethel, if you can upload a sample file with test data I'll be able to work something out, tis part of my job to do things like this.

[Jupitus]

If dates are in column A, and values are in column B, then the following formula cut n pasted in C helps:

=IF(TODAY()-A1<365,B1,0)

You can then autosum column C.

I believe that would sum the values of those in the past 365 days, which isn't quite the same as only selecting values in 2007, for example. Depends what Ethel wants to do.

Ethel, if you can upload a sample file with test data I'll be able to work something out, tis part of my job to do things like this.

Well that does the 'within a year of the current date', which is what Eths asked for... to do 'in the same year as current date' you would use:

=IF(YEAR(TODAY())=YEAR(A1),B1,0)

instead...

[Jammy]

Yea, I just came up with:

=IF(YEAR(C2)=2007, B2,"")

Where colum C has your dates, and column B has your data.

Substitue "2007" with the year you want to get results from.

[Matt]

smart arse

[Jupitus]

Pfft - that's an easy one!!

[Ethel]

Thanks again guys (n' guyesses),

I haven't actually started on it yet, thought I'd be lazy 'n get a few pointers before I tie myself in knots.

I did mean the preceding 365 days 'n it can spill over more than one sheet.

I'll post it up once I've got somewhere with it.

Yay for TMF - kicks International rescue's ass any day

[Jupitus]

Thanks again guys (n' guyesses),

I haven't actually started on it yet, thought I'd be lazy 'n get a few pointers before I tie myself in knots.

I did mean the preceding 365 days 'n it can spill over more than one sheet.

I'll post it up once I've got somewhere with it.

Yay for TMF - kicks International rescue's ass any day

The spilling over onto a new sheet isn't much of a problem although you may have to manually define the range for the totalling... the formula only references the cells to the immediate left without other dependancies. There are more sophisticated ways to do it but this one is the easiest, I would say.

[paulrockliffe]

I used the AVERAGEIF Function as follows:

"=AVERAGEIF(B4:B21,">C3",C4:C21)"

Where C3 contains the forumla "=TODAY()-365"

That should work, however it returns a "#DIV/0!" Error and I have no idea why?? Cells B4:B21 are full of dates and C4:C21 just numbers.

That's not much use to you, but if someone just corrects my mistake it'll work perfectly. Excel help says that this is a valid formula, "=AVERAGEIF(A2:A5,">250000",B2:B5)", so I'm not really sure what's going on????