# Help

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

=-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?

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