Any one well versed in excel?
I know the basics and basic formulas but needed assistance with a few formulas involving date arithmetic and sum of zero response (if/then) I can send a demo excel file if needed.
I need to add days of the week(numbers) till I get to the end of the month then go to 1 and continue to add days for a total of 4 weeks.
ex: starting on 12/27/09 and I need to get the results 27 28 29 30 31 1 2 3 4 5 6 7 8 9 etc. this will be based on the input of the date 12/27/2009.
I also need a formula to sum a column of data that is not numeric. If the space is blank I need it to be added to the total, if it if an “x” or an “h” I need it not included in the results
ex:
x
h
x
x
2 is the answer I want. basically I need it to count the blanks
if the letters were numeric I was thinking since I know how many lines there are (6) I could write =6-sum(a1:a6) which would provide the answer 2 but I need the if/then statement to make the h and x numeric
Am I making sense?
Al - there is no easy answer for what your trying to do except for that it is doable. Excel is SO much more powerfull than many people think especially with add-ins and macros. Im not sure how significant the project is that you are working, but if you want i have a book you can borrow that can show you some very eloquent and simple ways for modeling complex data. It might be overkill but let me know if your interested. Here is the link
I have a simpler book then that one.
currently I have this: =DATE(YEAR(D1),MONTH(D1),DAY(D1)+1) - to up the date but I only want to see the day result nothing else.
When I try I get the desired result till I hit 31, The next day goes 32 then 2,3,4,5. I can not get it to give a correct 1 vs.32.
Thanks Ted, everything is working well on those areas
The countblank is working well for the majority of the pages but I have have another section I am working on that uses a list of data for the input that at the bottom I need to sum of select characters. I assume I need to use an array but am not sure of the formula.
ex
L
2/7
G8
JD
X
All the entries are formatted as text and I need the total of select characters including the line with the blank space.
For sake of this example say I want to count (" ", L, 2/3) but wanted to ignore (G8,JD,x)
I found this array on the internet to count distinct values
=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""), IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))
I have yet to play with it to test its functionality but the description seems to fit what I am trying to do. I figure I would have to add/change this to ignore select data or view unwanted data as 0
Al if there is anything else let me know. I’ve done a lot in excel in the past and my Dad spends 5 days a week in excel for work only to come home to do excel for several charities he works for.(he also works in a hospital, so may be pretty familiar with what your working with)
Sorry I didn’t see this post before now. Hope everything is going well with your new position and all is well with the fam.