Forum Home
Press F1
Thread ID: 47985	2004-08-11 04:50:00	Excel formula	bpt1 (419)	Press F1

Post ID	Timestamp	Content	User
260421	2004-08-11 22:52:00	Once you know that Excel does shortcut TRUE and FALSE to 1 and 0 in arithmetical operations, that does seem to offer possibilties of simplifying entering of functions. The two suggestions made both work well as requested: andrew93: =SUM(IF(B2:B30="T",IF(C2:C30<5,A2:A30))) then press ctrl shift enter (array method) parry: =SUMPRODUCT(--(B2:B30="T")*(C2:C30<5),A2:A30) then press enter (non-array method, and the -- is optional, could just as well have used 1* or any equivalent ) Either of: =SUM((A2:A30)*(B2:B30="T")*(C2:C30<5)) then ctrl shift enter or =SUMPRODUCT((A2:A30)*(B2:B30="T")*(C2:C30<5)) then enter could also do the job, and so probably could other variations. Anyway there seems a number of adequate answers. Personally I do have a general preference for array functions because they do anything that ordinary functions can do and a lot more that ordinary functions can't. They're also usually shorter to write and only the pressing of ctrl-shift first takes more time.	rugila (214)
260422	2004-08-12 04:11:00	> Personally I do have a general preference for array > functions because they do anything that ordinary > functions can do and a lot more that ordinary > functions can't. > They're also usually shorter to write and only the > pressing of ctrl-shift first takes more time. I agree but unfortunately while you are very adept at using them most people arent. Really a training issue but if a book is distributed to a large # people you may wish to use non-array functions if possible as they often stuff it up by changing small parts of the formula to suit themselves and forgetting to CTRL+SHIFT+ENTER. An array entered value thats entered normally doesnt always return an error (#Value etc) but instead returns an incorrect value and so may not be picked up immediately. Its just something to take into consideration.	parry (27)
260423	2004-08-12 12:01:00	Thanks all you clever people. bpt1	bpt1 (419)
260424	2004-08-12 12:44:00	bpt1 >Thanks all you clever people. Much appreciated bpt1, we do our best. Parry, Thanx again for further comments. >I agree but unfortunately while you are very adept at using them most people arent. I make no claim to be adept at these. However I understand one major purpose of this forum is so those with some degree of adeptness do their best to assist others, rather than not doing things because they are allegedly adept. Am I wrong about supposing this? >An array entered value thats entered normally doesnt always return an error (#Value etc) but instead returns an incorrect value and so may not be picked up immediately. Its just something to take into consideration. I don’t find what you say very clear. Errors can occur in any attempt to enter any formula. What does “entered normally” mean? Are you suggesting that properly used array functions are more prone to error than properly entered non-array functions, alternatively that the errors are harder to pick up? At best, that’s very disputable. Perhaps if you give a specific example…. Regarding your own function above I’d make a couple of comments. But please don’t get me wrong. I agree with andrew93 that it’s a good function and achieves what was asked for in this particular case. Your formula relies on arithmetical operations, in particular it uses products, specifically it relies totally on the conditions that 0 x ? always equals zero and 1 x ? always equals ?, whatever ? may be. If these conditions are not true then your formula fails, and it’s not hard to envisage situations where they are not true, in fact it can be strongly argued that in general they are not true. (Check with the mathematics department of your local university if you’ve got any doubts on this). (Also, the two other formulae I gave above are just suggested variants of yours, and not intended to be anything else.) andrew93’s formula, although not necessarily error-free in all circumstances itself, is much less likely to encounter errors. If some logical conditions are not met the andrew93 formula just skips that line when doing its summation. Your formula (purports to) multiply by zero, at the same time implicitly and without saying so assumes that multiplying anything by zero always equals zero, and therefore that it always adds zero to the summation. (And did I detect some uncertainty in your own mind about the validity of your own operations when you put in, or weren’t sure whether you should put in, your double negative?) A lot of cases of using this approach in practice there’d be no problem. However, one can also think up some significant situations, levels of medical drug application to patients, financial decisions involving the odd hundred thousand, “safe” doses of radiation, and the like, where I at least would want to very, very sure that the chances of error in my calculations were minimised, and that I slid in as few unstated implicit assumptions into my formulae as possible. Just a few things to take into consideration.	rugila (214)
260425	2004-08-12 13:10:00	Just got one more question: How can I use "countif" instead of sumif?	bpt1 (419)
260426	2004-08-12 13:19:00	in exactly the same way as before - if we assume the same problem with the same data ranges and sum/count criteria then the original formula that I posted could be modified like so : =COUNT(IF(B2:B30="T",IF(C2:C30<5,A2:A30))) again using CTRL_SHIFT_ENTER plus I'm sure you could modify all of the variants posted by ruglia and parry in the same manner.	andrew93 (249)
260427	2004-08-12 13:26:00	Looking back over the sumproduct variants i got to thinking about the use of the "*" in the formula provided by parry and not the "," as you would expect with the sumproduct formula. Anyhow, if you do this : =SUMPRODUCT((B2:B30="T"),(C2:C30<5),A2:A30) then the formula does not work, but if you do this : =SUMPRODUCT(-(B2:B30="T"),-(C2:C30<5),A2:A30) then the formula does work. An example of forcing the value to be a value as suggested by parry. Fascinating!	andrew93 (249)
260428	2004-08-12 13:29:00	Thanks, that's great - I forgot the Ctrl Shft bit. bpt1	bpt1 (419)
260429	2004-08-12 13:32:00	No probs - although I would recommend going with a variant on parrys formula because it is more elegant than mine plus you won't have to worry about remembering the CTRL_SHIFT_ENTER thing.	andrew93 (249)
260430	2004-08-12 13:44:00	After reading Rugila's post on assumptions and errors I started to put all of the variants posted to date to the test, all of them can handle exceptions or data oddities in columns B & C but the sum and sumproduct formula with the double * return an error is there is a non-numeric value in column A.	andrew93 (249)
1 2 3