PDA

View Full Version : Excel formula



bpt1
11-08-2004, 04:50 PM
Can anyone help with a formula?
In Excel I want to sum a column of numbers when conditions in 2 adjacent columns apply. E.g. sum(a2:a30),if(b2:b30="T")and if(c2:c30,"<5")

andrew93
11-08-2004, 06:00 PM
Hi bpt1

Firstly, I must give credit to rugila for teaching me about array formulas in a previous thread, but this is what you are looking for :

Enter the following formula :
=SUM(IF(B2:B30="T",IF(C2:C30<5,A2:A30)))

BUT DO NOT click the tick or DO NOT press Enter but instead press CTRL-SHIFT-ENTER together.

If you don’t press the CTRL-SHIFT you just get a #VALUE!, if you do you should get the right answer.

This is an array formula which can only be entered in this way. I have tried it and it works.

All credit to rugila for this one - I lifted part of the answer from his post on this thread (http://pressf1.pcworld.co.nz/thread.jsp?forum=1&thread=49317&message=291832&q=#291832).

Andrew

Spartacus
11-08-2004, 06:19 PM
What are you wanting to do if any of the conditions are false?

Andrew's formula will successfully add all values where the two conditions in the same row are true, leaving out the values which have corresponding false conditions.

If you want to run the calculation only when ALL conditions are true, this may help (again, use ctrl-shift-enter to validate):

=IF(AND(B2:B30="T",C2:C30<5),SUM(A2:A30),"XX")

where XX is the text displayed if any conditions are false (use just "" to display a blank cell).

andrew93
11-08-2004, 09:18 PM
Have you tested your formula Spartacus? It doesn't work - it returns "XX" even if there are some valid entries.

rugila
11-08-2004, 10:49 PM
Andrew93:
Thanks for your overly generous comments. I'm pleased to be able to be of some help.


Spartacus:
I don't quite follow what you are getting at.
With Andrew93's function the value in column A is added if and only if both of the corresponding conditions in columns B and C are true.
If either of the B or C conditions (includes both) is not true then the value in column A is not added.
This seems to me exactly what was asked for and is just what the function does.
I don't see any other possibilities in the original problem as posed.

parry
11-08-2004, 11:23 PM
Hi, I think this should do it.

=SUMPRODUCT(--(B2:B30="T")*(C2:C30<5),A2:A30)

rugila
12-08-2004, 12:46 AM
Now that’s interesting Parry.

I never realised before that Excel treats the text words true and false as one and zero when doing arithmetical operations.

For example the function =true+true gives the answer 2, etc.

I always used expressions like =if(true,1,0) +if(true,1,0) etc. which is the same thing but clumsier.

Even works with things like =True^false and sqrt(true)

I guess we all learn as we go along, as we all should.

Don’t really see why you used the double negative tho’, except as a self-neutralising spacer to make it easier to read. Makes yours a bit clumsier than need be too .

andrew93
12-08-2004, 12:51 AM
ditto - i was puzzled by the double negative too - what's with it parry? cool funciton nonetheless.

BTW rugila - i was just giving credit where it was due.
:D

parry
12-08-2004, 08:14 AM
Hi Rugila & Andrew. Yes Rugila, your right I didnt need the double negative after all. The double negative is used to coerce a text value into 1's and 0's and so I have a habit of using this as some formulas fail with textual components.

Aladin Akyurek has some info on Sum Product here (http://www.mrexcel.com/wwwboard/messages/8961.html).

Spartacus
12-08-2004, 09:32 AM
>it returns "XX" even if there are some valid entries.

Like I said, it returns a valid result ONLY when ALL conditions are true - that is, all B2:B30 = T, and all C2:C30 < 5.

Again, it depends on the situation as to whether this is necessary - clearly in this case it isn't... :)

rugila
12-08-2004, 10:52 AM
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.

parry
12-08-2004, 04:11 PM
> 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.

bpt1
13-08-2004, 12:01 AM
Thanks all you clever people.

bpt1

rugila
13-08-2004, 12:44 AM
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.

bpt1
13-08-2004, 01:10 AM
Just got one more question: How can I use "countif" instead of sumif?

andrew93
13-08-2004, 01:19 AM
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
13-08-2004, 01:26 AM
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!

bpt1
13-08-2004, 01:29 AM
Thanks, that's great - I forgot the Ctrl Shft bit.

bpt1

andrew93
13-08-2004, 01:32 AM
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
13-08-2004, 01:44 AM
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.

parry
13-08-2004, 08:14 AM
> >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….

Hi Rugila, sorry if I wasnt clear on this. By normally entered I mean by just pressing the Enter key as opposed to Ctrl+Shift+Enter. Perhaps I should have called this the 'standard method'.

What I mean is that if you take Andrews array entered formula ...
=SUM(IF(B2:B30="T",IF(C2:C30<5,A2:A30)))

And supposing the values are as follows:-

A2:A8 = 1,2,3,4,5,6,7
B2:B8 = T,T,T,A,A,A,A
C2:C8 = 1,3,5,7,9,11,13

Then Andrews formula with Ctrl+Shift+Enter returns 3 as rows 2&3 are true. However if you use the same formula but just press Enter the formula returns 28, being the total of all column A regardless of the other 2 conditions.

So if whoever created the formula has other people using the workbook anbd someone need to say extend the range then if they dont press Ctrl+Shift+Enter the result changes. The cell is not in error and with a large range of data rather than just the 7 I gave above then this may not be noticed by anyone until much later.

This is what Im meaning. However, just because people may make mistakes is no proper argument (to me anyway) to not use an array, but is something you may wish to consider if many people are using the book. Of course you may opt to protect sheet etc.

All that I am attempting to do is make it clear for people reading this thread that you need to be careful when using arrays.

Graham L
13-08-2004, 03:51 PM
... "you need to be careful with using arrays" ...

"But the numbers come out of a computer, so they must be right."

In fact, you need to be careful when using computers. It's not arrays that are the problem. It's, as always, people. :D

Graham L
13-08-2004, 04:13 PM
You need to be very careful using spreadsheets. ]:)

Just for fun I tried "spreadsheet+errors" to Google. This paper by Panko (http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm) should be compulsory (scary) reading. I like his comment that the increase (to 91%) in the percentage of spreadsheets found with significant errors in studies after 1997 comes from improvement in the auditing methods. ;-)

parry
13-08-2004, 05:43 PM
Interesting stats Graham. Does that mean the other 9% were empty spreadsheets? :-)