Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | 3 | (show all)   Print Thread
Standard User cheshire_man
(knowledge is power) Thu 23-Jan-14 06:25:01
Print Post

Excel (2010) formula sought


[link to this post]
 
I'm trying to get an Excel (Excel 2010) formula for the following task:

Col.1 contains dates
Col.2 contains values

I need Cols.3-14 to contain the number of values in a range for each of the 12 months

I can achieve it by having another column - named Month - containing the Month value of Col.1 and then have in Col.3 the formulae

=COUNTIFS(Month,1,Values,">0",Values,"<=10")
=COUNTIFS(Month,2,Values,">0",Values,"<=10")

and so on for each of the 12 months.

I'm trying to achieve that without the need for a separate Month column, such that something like

=COUNTIFS(Month(Dates),"=1",Values,">0",Values,"<=10")

would work.

Any thoughts?

Tony
We have more and more laws, and less and less enforcement
Standard User ian72
(knowledge is power) Thu 23-Jan-14 09:00:26
Print Post

Re: Excel (2010) formula sought


[re: cheshire_man] [link to this post]
 
Your problem here is that the month function can't operate on a range.

The easiest ways to do this would be the way you currently are but put the months column either off to the right or on another sheet and hide it (so that people don't see the messiness) or expand the formula to:

=COUNTIFS(Dates,">=1/1/2014",Dates,"<1/2/2014",Values,">0",Values,"<=10")
=COUNTIFS(Dates,">=1/2/2014",Dates,"<1/3/2014",Values,">0",Values,"<=10")

etc (assuming you want a month in a particular year)
Standard User philippercival
(fountain of knowledge) Thu 23-Jan-14 13:20:09
Print Post

Re: Excel (2010) formula sought


[re: cheshire_man] [link to this post]
 
I have had fun with this and constructed a solution for something I wanted to do anyway which was to look at my solar results.

Unfortunately as Ian indicated I could not do it without the month column being present, though by right clicking at on the C at the top of the column and selecting hide, it becomes invisible.

My Solution is here if you want to have a look. Though I have to confess I did not totally picture what you were doing.

On my sheet
  • The values to be analysed go in columns A and B down to the row 2000.
  • It will work with just 1 row filled for checking (blank out the rest)
  • Currently 134 rows are filled with randomly generated values.
  • Fill the formula further down to experiment
  • To make the grid access more rows modify the formula in D3. and fill the formula in column C further down.
  • The values of the ranges can be modified by changing the numbers in D9 to D19
  • The total at the bottom right should always match the number of rows.


Well I had fun and learnt a couple of new things so I hope it may be of use to you as well.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2


Register (or login) on our website and you will not see this ad.

Standard User cheshire_man
(knowledge is power) Thu 23-Jan-14 14:07:59
Print Post

Re: Excel (2010) formula sought


[re: philippercival] [link to this post]
 
I need to study yours in more detail as I think I can learn a thing or two smile

Oddly enough my application is very similar.

Col.A contains dates, every day of the year is there, or will be as time goes by.
Col.B contains the minimum overnight temperature (F)
Col.C contains the minimum overnight temperature (C) - auto inserted by VBA

Then a block of cells to the right contain 12 columns, 1 per month; and 7 rows, 1 for each temperature range. You can see the spreadsheet here.

[As an aside, I quite like the formula I constructed in cell D1]

Tony
We have more and more laws, and less and less enforcement
Standard User ian72
(knowledge is power) Thu 23-Jan-14 14:38:58
Print Post

Re: Excel (2010) formula sought


[re: cheshire_man] [link to this post]
 
OK, there is a way to do this without the months column. You need to use an array formula rather than countifs. The formula for cell W6 (december >30 and <40):

=COUNT(IF(MONTH(Dates)=12,IF(DegreesF>=30,IF(DegreesF<40,1))))

However, because it is an array formula you have to use Ctrl-Shift-Enter to save the formula. The Dates range is one I created that covers all the dates in column A.

When you save it use Ctrl-Shift-Enter it will add curly brackets - {} - to start and end of the formula.
Standard User philippercival
(fountain of knowledge) Thu 23-Jan-14 15:12:00
Print Post

Re: Excel (2010) formula sought


[re: ian72] [link to this post]
 
Oh no!

I was planning to get on with some productive work, now you have opened a new can of worms for me to learn about.

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User ian72
(knowledge is power) Thu 23-Jan-14 15:20:48
Print Post

Re: Excel (2010) formula sought


[re: philippercival] [link to this post]
 
LOL. To be fair they aren't that difficult once you get your head around them - that was about 10 minutes for me and until then I hadn't ever looked at array formulas. But I might well use them in future wink

My problem was I was given a challenge and I find it very hard to resist them and I do so love playing with Excel formulas.
Standard User philippercival
(fountain of knowledge) Thu 23-Jan-14 15:23:32
Print Post

Re: Excel (2010) formula sought


[re: cheshire_man] [link to this post]
 
I like the idea of using the named ranges.

When I was doing mine I was trying to leave it open to adding more values at a later date. In my case running the dates down, rather than up the column makes this easier. (As an aside it makes the axis on the graph go from left to right rather than right to left as well.)

Then by using a formula (countif) to see how how many rows have been filled, you can use a formula to write the ranges as well and place these in a cell.

To apply the range in another formula you use the indirect function to look in the cell containing your range.

My direct challenge when I created my grid, was to put a single formula in the top left cell and then use fill to put it in all of the other cells.

As a matter if interest do you have some sort of weather station device to do all the recording for you?

http://speedtest.net/result/2459383290.png

Solar Results
Nov  143,  Dec  125,  Jan  77,  Feb 174,  Mar  210,  April 384, May 450, 
June 485, July 512, Aug 433, 
Max Day  25.2
Standard User cheshire_man
(knowledge is power) Thu 23-Jan-14 15:56:02
Print Post

Re: Excel (2010) formula sought


[re: ian72] [link to this post]
 
Excellent! That works - not that I was doubting you wink

I'd tried to get an array formula, but I was trying to do it with COUNTIFS.

Like you I enjoy 'playing' with Excel, I always have been someone who likes numbers.

Tony
We have more and more laws, and less and less enforcement
Standard User ian72
(knowledge is power) Thu 23-Jan-14 16:20:23
Print Post

Re: Excel (2010) formula sought


[re: cheshire_man] [link to this post]
 
Glad it works for you. Quite a neat little formula and something that will come in handy.

I started trying it with COUNTIFS and then realised that COUNTIFS is in many ways just the non-array version of the array COUNT formula.
Pages in this thread: 1 | 2 | 3 | (show all)   Print Thread

Jump to