MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help


Posted by Rob on October 04, 2001 11:17 AM

Can someone please help us simplify this computation. It's taking us to long to "compute".

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$G$2:$G$12600=$B$7,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$F$2:$F$12600=$A$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))-SUM((Raw_Data!$J$2:$J$12600=$I17)*(IF(Raw_Data!$H$2:$H$12600=$D$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))


Posted by Juan Pablo on October 04, 2001 12:04 PM

I suggest this one, see if it's the same result, i tried with some dummy data, but maybe i'm leaving something out.

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*((Raw_Data!$G$2:$G$12600=$B$6)+(Raw_Data!$G$2:$G$12600=$B$7)+(Raw_Data!$F$2:$F$12600=$A$6)+(Raw_Data!$H$2:$H$12600=$D$6,1,0))*(Raw_Data!AY$2:AY$12600/1000))

I hope that does it.

Juan Pablo

Posted by Barrie Davidson on October 04, 2001 12:09 PM

Can you put this formula to the right of your data in the Raw_Data worksheet and changing it to to read (maybe in cell BA1, assumes that your original formula was in worksheet named "Sheet1"):

=-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$G$2=Sheet1!$B$6*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$G$2=Sheet1!$B$7*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$F$2=Sheet1!$A$6*Raw_Data!AY$2/1000-Raw_Data!$J$2=Sheet1!$I17*Raw_Data!$H$2=Sheet1!$D$6*Raw_Data!AY$2/1000

Then, in Sheet1, you could sum up column BA. This will eliminate the problem of Excel having to evaluate the entire Raw_Data spreadsheet each time you make a change.

Do you think this would work for you?

Regards,
BarrieBarrie Davidson

Posted by Rob on October 04, 2001 12:41 PM

Juan, this worked except for one thing. There are four "choices" in column G. When B6 is filled and not 7, 8 and 9, it adds up the choice I entered and two of the others as well. Why?

Thanks in advance.

Posted by Juan Pablo on October 04, 2001 12:49 PM

I don't know... i saw one thing in the formula

=-SUM((Raw_Data!$J$2:$J$12600=$I17)*((Raw_Data!$G$2:$G$12600=$B$6)+(Raw_Data!$G$2:$G$12600=$B$7)+(Raw_Data!$F$2:$F$12600=$A$6)+(Raw_Data!$H$2:$H$12600=$D$6))*(Raw_Data!AY$2:AY$12600/1000))

In the $D$6 there was a ,1,0 left from the IF statement that you had, i erased it... hope that was the error.

Posted by Dan on October 04, 2001 12:51 PM

Juan

I ran some more queries. For some reason the calculation automatically pulls two of the criteria.

I like the formula, but I'm perplexed why this is happening. Any other suggestions?

Posted by Juan Pablo on October 04, 2001 12:56 PM

Re: Juan

Don't know..... can you send it to me so i can take a look at it ? it's better to work with the real data...

Juan Pablo