Another Excel question for you gurus... I have been recording my Electricity usage daily for the last six months or so, and about a month ago I realised that I was walking past the rain gauge, so I started adding that to the spreadsheet. So now, after a Month has passed, it strikes me that rather than transcribing it to the paper sheet I have been using for years, I could simply add another sheet to the workbook and do all my rainfall summaries there.
What I would like to add is a cell for each month that looks up my data entry column, and sums the cells for the month. For rainfall that would be (for example) =Sum(CellFor(2 Jan)...CellFor(1 Feb)) as I record rainfall early in the morning so the record is actually for the previous day. It is easy enough to do manually, but it would be neat if I could put a formula in to work out the correct start and end cells for the month (I have a column with the date in it) and do the sum for the month, and for that matter year to date, automatically. If it got really smart, it would also average each month year to year.
Last Edit: Feb 2, 2016 14:22:44 GMT -6 by GregDarcy
Main GCID: GregDarcy Secondary: GregDarcyToo All gems friends spoken for. Happy to accept friends for party hats. I blame Apple's auto incorrect for all my typos. I actively discourage challenges. All challengers are unfriended.
Oh, great idea, I love peonies! So much, that last spring, I brought home five small loads of stones -about twenty five at a time- carried them one by one to my back yard, and built a planter for my peonies. They are happier there in the partial shade and nice dirt. Sorry, getting a little off topic here...
I have something just for you...
Pike Market, Seattle, Summer 2015. Flower stand after flower stands filled w/ peonies. I was gaga (my new Valentine dragon's name) over them...
The white peony bouquet there was only 20.00 or 25.00 dlrs which would easily cost 80 or 100 in any flower shops, and most shops don't even sell them.
Peonies and orchids are my two all time loves. I have been to few international orchid shows, and seen rare orchids, pure Wows!
Oh my gosh, those are so beautiful! Thank you for sharing your picture! It is rare to see big, beautiful bouquets of peonies like that. At least down here in the south. Or maybe I just don't get out enough... And orchids are fascinating to me, those shows must have been wonderful. Now I'm thinking maybe I should check the Atlanta Botanical Garden schedule for this spring.
Post by wOMantraNOM on Feb 3, 2016 14:14:05 GMT -6
You should see the Orchid garden in St Louis, Buffy, it's in a beautiful conservatory at their Arboretum. I couldn't stop taking pictures (they're on a disc ... somewhere).
During one winter visit, we drove by the St Louis Zoo and I saw flamingos in the snow - beautiful memory ( but no pics).
gcid: womantrarae / team: Iron Blossom 🐉🌸🛠🌸🐉 Now offering: Various
Welcome to the Whine Lounge, please whinge creatively and bring cheese. ~featuring "utterly rifted l'aissez faire conversation pit" - happy hours 24/7~
"You see what you want to see. You hear what you want to hear." Rockman, in The Point, by Nilsson
Another Excel question for you gurus... I have been recording my Electricity usage daily for the last six months or so, and about a month ago I realised that I was walking past the rain gauge, so I started adding that to the spreadsheet. So now, after a Month has passed, it strikes me that rather than transcribing it to the paper sheet I have been using for years, I could simply add another sheet to the workbook and do all my rainfall summaries there.
What I would like to add is a cell for each month that looks up my data entry column, and sums the cells for the month. For rainfall that would be (for example) =Sum(CellFor(2 Jan)...CellFor(1 Feb)) as I record rainfall early in the morning so the record is actually for the previous day. It is easy enough to do manually, but it would be neat if I could put a formula in to work out the correct start and end cells for the month (I have a column with the date in it) and do the sum for the month, and for that matter year to date, automatically. If it got really smart, it would also average each month year to year.
You want to automatically make the spreadsheet calculate the total rainfall for the month, but the spreadsheet automatically knows which dates should be added up in which cells? Well, I think it's easy enough to do manually, but you could use a lookup tool.
First, write the month name in a separate cell, I.e January. For now, I'll name this cell A1 for reference. Ensure that all your dates have the month name in, or the one you typed in A1 anyway. Next, plug in this code in the place you want total rainfall for that month to be in: =SUM(VLOOKUP(A1,~lookup range~,~column with rainfall figures in~,FALSE)) In this the lookup range has to include the full table array of the dates and the rainfall figures for each day, for example B1:C3000 (just use a large number to secure you never miss a date - that way you don't need to change it when copying this code for different months.) And the column with rainfall figures has too be a number format, like how VLOOKUP is normally done - so if your lookup range was B1:D5000 and the rainfall figures are in column C, it's 2, if they are in Column D, it's 3 etc. So an example would be: =SUM(VLOOKUP(A1,A2:C3000,3,FALSE)). Make sure you hit ctrl + shift + enter, rather than just enter, else it won't work.
The averaging can be done by collecting a figure for the number of days in that month, using the COUNTIF function (a quick google search should net a few results. Once you have done that, simply divide the cell with the total monthly rainfall by the cell with the number of days in that month and ta da, average monthly rainfall has been done. This can be replicated between months simply by changing a few values.
Hope This helps.
No longer active on DragonVale! *briefly back online*
Thanks JV. It will be a day or two before I will be able to work through it though.
Question: I thought vlookup returned a value. I think I need a cell address I need = sum(AddressOfRainfallForFirstDayOfMonth...AddressOfRainfallForFLastDayOfMonth) With the spreadsheet working out the two addresses based on the time/date stamp in the first column Or equivalent. Or can VLookup return a sum of a range?
But yes it is a good idea to build the required search using the column(month) row(year) headings
The spreadsheet as it is is already up on DropBox as I share it with my solar installer. I will post a link to it when I get on to a real computer.
Main GCID: GregDarcy Secondary: GregDarcyToo All gems friends spoken for. Happy to accept friends for party hats. I blame Apple's auto incorrect for all my typos. I actively discourage challenges. All challengers are unfriended.
GregDarcy just saw your excel posts. Post a link and I think I will be able to work out a formula if I'm understanding the request & your set up correctly.
Another Excel question for you gurus... I have been recording my Electricity usage daily for the last six months or so, and about a month ago I realised that I was walking past the rain gauge, so I started adding that to the spreadsheet. So now, after a Month has passed, it strikes me that rather than transcribing it to the paper sheet I have been using for years, I could simply add another sheet to the workbook and do all my rainfall summaries there.
What I would like to add is a cell for each month that looks up my data entry column, and sums the cells for the month. For rainfall that would be (for example) =Sum(CellFor(2 Jan)...CellFor(1 Feb)) as I record rainfall early in the morning so the record is actually for the previous day. It is easy enough to do manually, but it would be neat if I could put a formula in to work out the correct start and end cells for the month (I have a column with the date in it) and do the sum for the month, and for that matter year to date, automatically. If it got really smart, it would also average each month year to year.
ETA: Nvm... this isn't what you're looking for. This sumproduct for the months can't distinguish the years, so it would add up every January and so on. This will work on a year to year basis.
a cell for each month that looks up my data entry column, and sums the cells for the month
Okay... I did a rough, rough mock up... (the rainfall values were blindly chosen...)
A
B
C
D
E
F
G
1
Date
Rainfall
Month
Total Rainfall per Month
Year
Total Rainfall per Year
2
1/1/2016
12
1
=SUMPRODUCT((MONTH($A$2:$A$7)=D2)*($B$2:$B$7))
2016
=SUMPRODUCT((YEAR($A$2:$A$7)=F2)*($B$2:$B$7))
3
1/31/2016
4
2
=SUMPRODUCT((MONTH($A$2:$A$7)=D3)*($B$2:$B$7))
4
2/1/2016
79
3
=SUMPRODUCT((MONTH($A$2:$A$7)=D4)*($B$2:$B$7))
5
2/29/2016
21
6
3/1/2016
35
7
3/31/2016
7
If that is the set up you're looking for, you can copy & paste =SUMPRODUCT((MONTH($A$2:$A$7)=D2)*($B$2:$B$7)) and drag it down to fill the rest of the column.
for that matter year to date, automatically
If this is what you're looking for year to date... =SUMPRODUCT((YEAR($A$2:$A$7)=F2)*($B$2:$B$7))
ETA again
Since there are two criterion: Month & Year the simplest way I thought of setting it up requires that Month/Day/Year are split up into their own columns.
Oh, great idea, I love peonies! So much, that last spring, I brought home five small loads of stones -about twenty five at a time- carried them one by one to my back yard, and built a planter for my peonies. They are happier there in the partial shade and nice dirt. Sorry, getting a little off topic here...
I have something just for you...
Pike Market, Seattle, Summer 2015. Flower stand after flower stands filled w/ peonies. I was gaga (my new Valentine dragon's name) over them...
The white peony bouquet there was only 20.00 or 25.00 dlrs which would easily cost 80 or 100 in any flower shops, and most shops don't even sell them.
Peonies and orchids are my two all time loves. I have been to few international orchid shows, and seen rare orchids, pure Wows!
Peonies are gorgeous. My mom always had several plants in her garden. There was one plant in the front yard of a house we bought, I managed to kill it. Think I put too much mulch too close to the roots.
Another Excel question for you gurus... I have been recording my Electricity usage daily for the last six months or so, and about a month ago I realised that I was walking past the rain gauge, so I started adding that to the spreadsheet. So now, after a Month has passed, it strikes me that rather than transcribing it to the paper sheet I have been using for years, I could simply add another sheet to the workbook and do all my rainfall summaries there.
What I would like to add is a cell for each month that looks up my data entry column, and sums the cells for the month. For rainfall that would be (for example) =Sum(CellFor(2 Jan)...CellFor(1 Feb)) as I record rainfall early in the morning so the record is actually for the previous day. It is easy enough to do manually, but it would be neat if I could put a formula in to work out the correct start and end cells for the month (I have a column with the date in it) and do the sum for the month, and for that matter year to date, automatically. If it got really smart, it would also average each month year to year.
ETA: Nvm... this isn't what you're looking for. This sumproduct for the months can't distinguish the years, so it would add up every January and so on. This will work on a year to year basis.
a cell for each month that looks up my data entry column, and sums the cells for the month
Okay... I did a rough, rough mock up... (the rainfall values were blindly chosen...)
A
B
C
D
E
F
G
1
Date
Rainfall
Month
Total Rainfall per Month
Year
Total Rainfall per Year
2
1/1/2016
12
1
=SUMPRODUCT((MONTH($A$2:$A$7)=D2)*($B$2:$B$7))
2016
=SUMPRODUCT((YEAR($A$2:$A$7)=F2)*($B$2:$B$7))
3
1/31/2016
4
2
=SUMPRODUCT((MONTH($A$2:$A$7)=D3)*($B$2:$B$7))
4
2/1/2016
79
3
=SUMPRODUCT((MONTH($A$2:$A$7)=D4)*($B$2:$B$7))
5
2/29/2016
21
6
3/1/2016
35
7
3/31/2016
7
If that is the set up you're looking for, you can copy & paste =SUMPRODUCT((MONTH($A$2:$A$7)=D2)*($B$2:$B$7)) and drag it down to fill the rest of the column.
for that matter year to date, automatically
If this is what you're looking for year to date... =SUMPRODUCT((YEAR($A$2:$A$7)=F2)*($B$2:$B$7))
ETA again
Since there are two criterion: Month & Year the simplest way I thought of setting it up requires that Month/Day/Year are split up into their own columns.
Because you're looking up multiple criteria you'll have you press Ctrl-Shift-Enter
I have a solution already thanks to a few other forum members i added two extra columns as you did MonthColumn =MONTH(DateStamp - 1) because rainfall figure is actually the previous day's rain. YearColumn =YEAR(DateStamp - 1)
Then in my summary table I put CellThisMonth =SUMIFS(RainfallColumn,MonthColumn,ThisCellColumnHeader,YearColumn,ThisCellRowHeader) this adds the numbers in the Rainfall Column IFF the month and year match the summary cell in question
Actually a little more involved than that as the month header is Jan...Dec and I needed to convert to 1...12 and I do a little nonsense to keep the cells blank until there is actual data available. Sadly zero means no rain. Blank means no data so they are not the same thing.
YearToDate I simply sum the row Jan...Dec for the year Average I won't need to worry about for a year or so, but should be pretty straight forward.
i will post a link to the table if you want to see it. It is a lot messier than this as it is all cell references not neat labels.
Last Edit: Feb 4, 2016 22:50:38 GMT -6 by GregDarcy
Main GCID: GregDarcy Secondary: GregDarcyToo All gems friends spoken for. Happy to accept friends for party hats. I blame Apple's auto incorrect for all my typos. I actively discourage challenges. All challengers are unfriended.
Peonies are gorgeous. My mom always had several plants in her garden. There was one plant in the front yard of a house we bought, I managed to kill it. Think I put too much mulch too close to the roots.
Now that you just built a new house, you can always plant few bushes of peonies.
I spent 160.00 for 5 peony bulbs last year, "exotic color, and species...etc" so they said. They didn't amount to anything. I am hoping this year will yield better results.
Some friends touch your heart in a way that you can never erase.
Peonies are gorgeous. My mom always had several plants in her garden. There was one plant in the front yard of a house we bought, I managed to kill it. Think I put too much mulch too close to the roots.
Now that you just built a new house, you can always plant few bushes of peonies.
I spent 160.00 for 5 peony bulbs last year, "exotic color, and species...etc" so they said. They didn't amount to anything. I am hoping this year will yield better results.
Peonies need time to settle. Sometimes a few years. They will come. You just have to be patient.
dragon list (last dragon : Gatren) DV friend id: Crumble#7733 friend link coop friends always welcome, but all my gems are spoken for
Peonies are gorgeous. My mom always had several plants in her garden. There was one plant in the front yard of a house we bought, I managed to kill it. Think I put too much mulch too close to the roots.
Now that you just built a new house, you can always plant few bushes of peonies.
I spent 160.00 for 5 peony bulbs last year, "exotic color, and species...etc" so they said. They didn't amount to anything. I am hoping this year will yield better results.
koala, hope they are spectacular this year.
Not too much room for plants on our lot. Will have to look into the light and soil needs for peonies. Went from a spacious 5 1/2 acres to just barely 1/4 that is just 50' wide, 40' of it is house.
Posts: 4,392
Platform: iPad Accepting New Gifts: Thanks friends; this game now a solid chore, no longer a delight sadly, so taking an indefinite break. Koala to all. Forum Name to Tag: kiwichris01
Ooohhhhh just beautiful Crumble; do you know if they've been successful in breeding fragrance into peonies yet? My understanding is that they're not fragrant, but can't test that where as I live as its too warm all year round to grow them..... So I'm an olde roses grower instead
Ooohhhhh just beautiful Crumble; do you know if they've been successful in breeding fragrance into peonies yet? My understanding is that they're not fragrant, but can't test that where as I live as its too warm all year round to grow them..... So I'm an olde roses grower instead
Your post made me worry that I translated peony incorrectly. In German we have "Pfingstrosen". And some of them are the most fragnant of all my roses I have. Not the one on the photo though. But its neighbour, a pink one, has a very very strong, sweet and beautiful fragrance. I like it (the fragrance) that much that I sometimes pick one and put it in a vase which I normally don't do because it feels a bit like killing the blossom and it often then wilt much faster than outdoors.
dragon list (last dragon : Gatren) DV friend id: Crumble#7733 friend link coop friends always welcome, but all my gems are spoken for
Ooohhhhh just beautiful Crumble; do you know if they've been successful in breeding fragrance into peonies yet? My understanding is that they're not fragrant, but can't test that where as I live as its too warm all year round to grow them..... So I'm an olde roses grower instead
Your post made me worry that I translated peony incorrectly. In German we have "Pfingstrosen". And some of them are the most fragnant of all my roses I have. Not the one on the photo though. But its neighbour, a pink one, has a very very strong, sweet and beautiful fragrance. I like it (the fragrance) that much that I sometimes pick one and put it in a vase which I normally don't do because it feels a bit like killing the blossom and it often then wilt much faster than outdoors.
I'm no expert, but after a quick search in the peony field, most people talk about them having a nice sweet odour - so Kiwichris I can only assume that peonies do have fragrances
No longer active on DragonVale! *briefly back online*
Some peonies have light pleasant sweet scents, not at all overpowering, but many peonies are not scented at all.
Thanks for the beautiful picture of the peonies, crumble608 . Peonies are just gorgeous, showy, and happy flowers
My sister lives in Greenwich, London, just about 5 mins walk to the prime meridian line, Greenwich park, and the Royal Observatory. She spends lot of time taking care of her traditional English garden, and has all kind of scented flowers. Peonies are no exception. She has several large bushes of peonies.
Posts: 4,392
Platform: iPad Accepting New Gifts: Thanks friends; this game now a solid chore, no longer a delight sadly, so taking an indefinite break. Koala to all. Forum Name to Tag: kiwichris01
Great info sharing, thanks all Sadly where I'm living in NZ is becoming even warmer w climate change, so not only peonies but even my stonefruit trees are struggling to produce fruit with flavour ... Hafta switch to growing subtropicals Please inhale lungfuls of fragrance on my behalf!
You should see the Orchid garden in St Louis, Buffy , it's in a beautiful conservatory at their Arboretum. I couldn't stop taking pictures (they're on a disc ... somewhere).
During one winter visit, we drove by the St Louis Zoo and I saw flamingos in the snow - beautiful memory ( but no pics).
I bet the orchids are very beautiful, and would love to visit the Orchid garden.
Flamingos in the snow....wow, striking colors, n.i.c.e., I must agree.
Some friends touch your heart in a way that you can never erase.
Post by authorseven on Feb 15, 2016 18:17:57 GMT -6
If anyone here is interested in interviewing for a book on DV and the entire DV community, please send an email to TheDVguide at gmail dot com. The book will be released on Amazon as an ebook, and eventually in other platforms. Certain players will be spotlighted. Photos, drawings, etc welcome. Players may choose to remain anonymous but if they remain that way, I can't Spotlight them. I could use your input, and thanks in advance. Again, if you want an interview sent to you, please send your email to the address above. poppy, Fletch and Bloodstne you have messages from me in your inboxes.
Last Edit: Feb 15, 2016 18:22:37 GMT -6 by authorseven
Thank you, cyn. That was beautiful. I had never heard of him before. I'll now be sharing this with my guitarist son & grandson. Music well played can truly transport us.
'... overwhelming goodwill, and witty humour, profound grace and compassion blended with the spice of relentless taunts.'
Note: This is an independent forum and is not affiliated with Deca Games, the developer of DragonVale.
It was created by and is managed by DragonVale fanatics.
All who observe the following brief guidelines are welcome to this great community: no personal attacks & no spam.
Sorry, getting a little off topic here...
And orchids are fascinating to me, those shows must have been wonderful. Now I'm thinking maybe I should check the Atlanta Botanical Garden schedule for this spring. 
Think I put too much mulch too close to the roots.
