Posted in: Software
janedoe1981 asked:
Basically what I’m looking for is either a macro or formula to help me use data more efficiently in excel. In one instance, I am working off a receivables aging but rather than having it set up as you would normally think of it, I have the amount due in one column and then the number of days past due in a separate column. So I need to be able to count all of those items 30 days past due, 30-60 days past due, etc and I also need to be able to sum the items 30 days past due, etc. I typically use COUNT and SUMIF but I can’t get those to work when I need to say greater than 30 but less than 60 and greater than 60 but less than 90. I know I must be writing something incorrectly.
Basically what I’m looking for is either a macro or formula to help me use data more efficiently in excel. In one instance, I am working off a receivables aging but rather than having it set up as you would normally think of it, I have the amount due in one column and then the number of days past due in a separate column. So I need to be able to count all of those items 30 days past due, 30-60 days past due, etc and I also need to be able to sum the items 30 days past due, etc. I typically use COUNT and SUMIF but I can’t get those to work when I need to say greater than 30 but less than 60 and greater than 60 but less than 90. I know I must be writing something incorrectly.
I also have a spreadsheet where I have a column of dates and I need to know how to sort it into separate quarters. So I need a formula/macro that would look at each date and separate it into 1st quarter 2009, 2nd quarter 2009 and so on.
On another note, I have been thrown into a role at work where I’ll be doing all of this on a regular basis. I only know basic excel formulas so I could use a course on macros and formulas but I can’t seem to find a good one. I tried a community excel course but it was too basic for me. I looked at college courses but no one seems to offer an advanced excel course. Any ideas?
Sorry for being so long.
March 26th 2009
You could use:
=COUNTIF(A1:A53,”
March 28th 2009
long?
well you didnt actually give any ranges or a sample, so how about this
you say you can do a sumif(), and if you can get that to work, then thats a good start. its just the part between dates thats the problem?
you may just need to subtract
=sumif(formula greater then 60 days)-sumif(formula greater then 90 days)
in other words…the 1st formula greater then 60 will include everything greater then 90…then you subtract the amount greater then 90 and you will be left with the amount between 60 and 90.
hope that makes sense, because you have to follow the same logic for the 30 days.
or else a sumproduct() usually does the trick.
as far as the quarters go…i would suggest a pivot table
finally, macros…i learn by creating them and manually adjusting them. some youtube videos have good demos.
if i cant figure it out, i just post the macro and ask if anyone can help adjust it…that works. i used the microsoft newsgroup for that…dont know why i havent used Y!A…maybe next time.
good luck.