PDA

View Full Version : Calculation problem



austenr
08-23-2007, 07:32 AM
In the attached WB, in column E, a 1 is entered if the student is a foreign student. This triggers the formula in Column AB to populate that cell with the word Foreign (it defaults to "US"). Now notice the formula in D4 and D5. I need a formula that calculates the same way D4 and D5 do for cells F4 and F5. How can you do the kind of calculation done in D4 and D5 because you cannot divide by a text string? I know you cannot do that but is there a way to do it another way? Thanks

Bob Phillips
08-23-2007, 07:41 AM
=SUMPRODUCT(--($AB$9:$AB$128=$E4),$V$9:$V$128,$F$9:$F$128)

But I also don't get the formula in V9

=(COUNTIF(S9:U9, ">0"))+(COUNTIF(S9:U9, "<0"))

and W9

=SUM(S9:U9)

because as far as I can see, T9 and U9 are not numbers.

austenr
08-23-2007, 07:45 AM
Me either. Was just asked to make these modifications. So your formual goes in both F4 and F5?

Bob Phillips
08-23-2007, 07:53 AM
It goes in F4, and copied down to F5.

austenr
08-23-2007, 08:02 AM
Works like a charm. The formula king!!!:bow:

austenr
08-23-2007, 08:12 AM
Hold on, Im not seeing the formula populating the cell with any numbers. Am I missing something here?

Bob Phillips
08-23-2007, 08:29 AM
Because all of those numbers in column V are all zero, which are zero because S is zero.

austenr
08-23-2007, 09:14 AM
:banghead: :dau: