Home |
Search |
Today's Posts |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Bob,
Good to hear from you. I felt kind of lonely yesterday talking to myself on the FREQUENCY( ) thread :) but on the other hand, I was so glad that I solved my puzzle all by myself by doing more experiments and using evaluate formula. I have found a way to shrink that formula to 1/3 by using SUM and 1/COUNTIF etc. However, I have some questions. But, haven't decided whether I should post on the other board or here. Any advice as to where? Sorry folks for the distraction. Back to SUMPRODUCT. Yes, I am a fan of SUMPRODUCT (with * if I may) <g I totally agree with your comments. I don't know if some may think that SUMPRODUCT is more advanced than SUM(IF()). The truth is I find it easier (even for a new user like me) to compose the SUMPRODUCT formula. Using the example on this thread, I probably would have left out ,l in the SUM(IF()) formula. If I had not known about SUMPRODUCT, I would be so lost looking at the SUM formula. For the record, I entered all *four* formulas on my spreadsheet of 10 records. They appeared to give the same results every time I changed my data. It was fun watching. I hope Keith can give us a feedback once he has found out the culprit. It'll be interesting to know. Earlier, I left out something as minor as a bracket and it was totally off. You are right, it is easier for me to see the connection between SUM(IF()) and SUMPRODUCT. This is from day one when I started to learn SUMPRODUCT. But I have a problem connecting SUM to SUMPRODUCT. Today is the odd time that I see the two being so close. So, to keep things simple, it is either SUMPRODUCT or SUM(IF()) for me. I could sense it that you tried to keep your formula as close to Keith's as possible. As a poster, I would appreciate your sensitivity especially when I am trying to figure out what is wrong with my formula. Hurray! I can hold on to SUMPRODUCT a little bit tighter now; still no complete confidence. I guess practice makes perfect. Thanks a lot for your paper which I have digested a large percentage; I haven't studied the examples at the end. I apologize to those who don't like length nor distraction. Until we meet again ...... Epinn "Bob Phillips" wrote in message ... Hi Epinn, You are absolutely correct, it can also be done with SP. And I think we would both agree that it looks simpler and more logical in SP than in SUM(IF(... <bg. And you are right, you rarely ever need to use CSE for SP, as it intrinsically deals with arrays. And again, you have seen the connection between SUMPRODUCT and SUM(IF( .... you are really getting this <g. I did it the way that I did it, not using SUMPRODUCT, and not reducing fully as Bernie did, as I was trying to help Keith overcome the problems in his formula. As such, I tried to keep as close as possible to his original formula so that he would better understand the step-change in the formula whilst removing all of the fluff. At least, that is my excuse <vbg. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Keith, Have you tried to apply your formula to say about 10 records and do evaluate formula and watch the steps? I solved quite a few of my puzzles using evaluate formula. I know you want to use SUM, IF etc. and I respect that. However, I want to talk to my respected teacher, Bob, about SUMPRODUCT for a minute if you don't mind. You can ignore the rest of this post if you like. Sorry for the intrusion. Bob, I am so happy because I seem to be able to use SUMPRODUCT to achieve the same result. =SUMPRODUCT((A1:A30000 = D7)*(B1:B3000038717)*(C1:C30000<E1:E30000)) (No need to enter as an array formula.) At first I couldn't get my formula working and I thought I forgot about Ctrl+Shift+Enter but then SUMPRODUCT didn't need it. What I missed was the *outside brackets*. What a big difference! I also realize that my formula is very similar to Bernie's. The only difference is for SUM we enter the formula as an array formula whereas for SUMPRODUCT there is no need. I didn't know that SUM alone (i.e. without IF) could be this similar to SUMPRODUCT. Thanks, Bernie. I feel an urge to say something when I have made a discovery. Thank you for reading. Epinn "Bob Phillips" wrote in message ... In my (limited) testing it seems to work fine, but it can be considerably simplified =SUM((IF((A1:A30000=D7)*(B1:B3000038717)*(C1:C300 00<E1:E30000),1))) still array-entered -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "KR" wrote in message ... I have a large worksheet and I'm trying to pull some specific information out of it, and I'm not getting the numbers I expected- so I think there is something wrong with my syntax. I'm hoping that someone can help point out my error. As the actual references are quite long, I've shortened them here to just show the logic, to determine if the logic itself if bad ={sum((if(A1:A30000 = D7,1,0))*(if(B1:B3000038717,1,0))*(if(C1:C30000<E 1:E30000,1,0)))} I had expected this to calculate each if statement to a 1 or 0, them multiply the outcome of those three if statements (so I'd get a value of 1 if all were true, or zero if any were false), then sum the number of lines where all three conditions were true. The actual number I'm getting is somewhere between 2x and 4x the number I'd actually expect based on the data, so either my logic is bad, or I have some other error. If no-one points out any errors in the above logic (multiplying and summing the if statements) then I'll re-post with the actual (longhand) formula to see if there is some other problem. Many thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
array formula(s)- not working, need some help please (problem with an 'AND') | Excel Worksheet Functions | |||
Creat a formula for multiplying a formula | Excel Discussion (Misc queries) | |||
Array formula | Excel Discussion (Misc queries) | |||
Nested IF statement with cell range reference | Excel Worksheet Functions | |||
Help on a formula | Excel Worksheet Functions |