|
|
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
|
|
|
|
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)
|
|
|
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.
|
|
|
I need to study yours in more detail as I think I can learn a thing or two 
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
|
|
|
|
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.
|
|
|
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
|
|
|
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 
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.
|
|
|
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
|
|
|
Excellent! That works - not that I was doubting you
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
|
|
|
|
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.
|
|
|
I haven't had much time to apply all the changes to my spreadsheet that this thread has provoked.
However I didn't like the idea of 01/01/2013 or =DATE(2013,1,1) in the column header to show the month under analysis, I didn't like it being year specific, so I tried =DATE(,1,) and, to my slight surprise as the Help says Year and Day are required, that works - in Excel 2010 at least.
Tony
We have more and more laws, and less and less enforcement
|
|
|
Replying to myself to update this thread and to ask another question.
I said previously that =DATE(,1,) worked. Oh no it didn't. The year it generated was wrong. Anyway I found a different way of achieving what I wanted.
However another little oddity reared its head.
Using the array formula in Ian72's post, I extended it further. That's when I found an apparent oddity.
Basically I wanted 4 conditions to be true so an AND() function seemed appropriate. Entering
=COUNT(IF(AND(YEAR(Dates)=YEAR(J$38),MONTH(Dates)=MONTH(J$38),DegreesF>$T41,DegreesF<$T42),1))
as an array formula didn't work, it always gave a result of 1.
However entering
=COUNT(IF(YEAR(Dates)=YEAR(J$38),IF(MONTH(Dates)=MONTH(J$38),IF(DegreesF>$T41,IF(DegreesF<$T42,1)))))
as an array formula gave me the result I wanted.
Why the difference?
As far as I can see they are logically the same, unless it's something to do with setting it as an array formula.
Any ideas?
Tony
We have more and more laws, and less and less enforcement
|
|
|
|
The array formula makes it run the function once for each component of the array. However, the AND I think will stop this and it will only actually run once for the whole array. So, with the AND only running once it will always give an answer of 1.
Essentially it has to step through the function for each entry in the array but the AND function stops that from happening as it is not compatible with array formulas.
|
|
|
Thank you kindly
Tony
We have more and more laws, and less and less enforcement
|
|
|
|
Shouldn't the AND come before all the conditions (i.e. before the IF)?
|
|
|
|
No, the IF can only take one conditional argument. The AND is to group all 4 conditions together as a single conditional argument to the IF. The logic is correct, it just isn't possible to enumerate an array using AND from what I can tell (I'm sure if MS wanted it to work they could make it but it just isn't the way Excel is built).
|
|
|
Fairy nuff.
I didn't get my head too far into the whole formula. Not easy when you haven't been following the thread.
http://onlinelibrary.wiley.com/doi/10.1002/978111839...
Using multiple IF functions for conditional aggregation: This example applies more complicated conditions with several IF functions. Let�s say we only want to calculate the average difference of the category CX and the difference should be more than eight. You will probably consider using the IF function with the AND function in the array function as shown in cell E3 of Figure G.6. The cell F3 illustrates the array formula applied in the cell E3. The array formula will always return 0. This is because both the AND function and the OR function cannot work properly in array formulas and the input is treated as a series of arguments instead of arrays. This also happens in other aggregation functions, such as SUM, AVERAGE, and COUNT. Thus, you need to consider an alternative approach. In this example, we use multiple IF functions to work for the AND operation.
Fair dos to you. I always double check Excel "that can't be done" statements. Some of the time, there's a trick to making things work.
|
|
|
Fair dos to you. I always double check Excel "that can't be done" statements. Some of the time, there's a trick to making things work.
I must admit I didn't look it up so nice to see some confirmation. My guess was based on how I expected the functions to work (ie how I would probably code it if I was the developer). My intuition for these things is usually not too bad.
|
|
|
Thanks for that, a useful link.
My Bibles on Excel & the various formulae and functions can be read to imply that, but your link explains it much more clearly.
Mind you, I think it could still be argued to be a bug....
Or a design feature....
Tony
We have more and more laws, and less and less enforcement
|
|
|
Bibles? F1
|
|
|
Bibles? F1 Excel Bible
Not sure what your F1 reference is to [confused
Tony
We have more and more laws, and less and less enforcement
|
|
|
Open Excel and hit F1.
|
|
|
Doh!  And there's me thinking motor racing 
And to quote from the Help You cannot use the AND and OR functions in array formulas directly because those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition. Nevertheless I still like paper books so that I can browse without having to stare at a screen. Ok, so call me old-fashioned 
Tony
We have more and more laws, and less and less enforcement
|
|
|
A bit of playing around has given me another 2 ways (albeit both much the same) to achieve the same result without using nested IFs.
The original formula was
=COUNT(IF(YEAR(Dates)=YEAR(J$39),IF(MONTH(Dates)=MONTH(J$39),IF(DegreesF>$AH42,IF(DegreesF<$AH43,1)))))
the first alternative is
=SUMPRODUCT((YEAR(Dates)=YEAR(J$39))*(MONTH(Dates)=MONTH(J$39))*(DegreesF>$AH42)*(DegreesF<$AH43))
and a slight variation, entered as an array formula, is
=SUM((YEAR(Dates)=YEAR(J$39))*(MONTH(Dates)=MONTH(J$39))*(DegreesF>$AH42)*(DegreesF<$AH43))
Tony
We have more and more laws, and less and less enforcement
|
|
|
|
I imagine an experienced pivot table user would get the desired info far quicker. Pivot tables are not my thing, but it seems to be a PT scenario.
|
Pages in this thread: 
Print Thread
cheshire_man
