Substitute a numeric value for a text value in a calculation.
Good deal. Glad we got it straightened out!
Biff
"Jeff" wrote in message
...
That's the ticket, exactly what I was looking for, thank you very much.
--
Jeff
"T. Valko" wrote:
Hold on there for a second....
I misunderstood what you were trying to do.
I think this is what you want:
=SUMPRODUCT(B33:B39,C33:C39)+SUMPRODUCT((C33:C39=" N/A")*5,B33:B39)
Biff
"T. Valko" wrote in message
...
Ok...try this:
=SUMPRODUCT(B33:B39,C33:C39)+COUNTIF(C33:C39,"N/A")*5
Biff
"Jeff" wrote in message
...
example was just that, I could have been clearer, let me restate the
formula
=(((C33*$B33)+(C34*$B34)+(C35*$B35)+(C36*$B36)+(C3 7*$B37)+(C38*$B38)+(C39*$B39)))
the values in Column B could be 10 through 1
the values in Column C could be 5,4,3,2 or N/A
Yes, N/A is a text value
--
Jeff
"T. Valko" wrote:
Your explanation mentions columns A, B and C and your formula
references
column G but then your IF formula references column F.
???
Looking at your formula columns C and G are numeric. Which one of
those
columns will contain the N/A?
Assume column G will contain the TEXT value N/A (not the error value
#N/A):
=SUMPRODUCT(C33:C39,G33:G39)+COUNTIF(G33:G39,"N/A")*5
Biff
"Jeff" wrote in message
...
Basically I would like to be able to create a scorecard with the
following
parameters.
Column A contains an Action could be anything (example parked car)
Column B contains the weighted value of that action in relation to
other
actions (1-10 )
Column C contains a numeric score for that action.
Excellent 5
Good 4
Fair 3
Poor 2
N/A N/A
I would then like to take the data and determine a "score" for the
sum
of
the actions.
=(((G33*$C33)+(G34*$C34)+(G35*$C35)+(G36*$C36)+(G3 7*$C37)+(G38*$C38)+(G39*$C39)))
The issue I have run into is that I would like to be able to enter
N/A
as
a
value but when the calculation is done have the value of N/A = 5.
Is there a nice clean way to do this?
I tried inserting =IF(F4="N/A",5,F4) into the formula but that was
messy
and
didn't work, not sure why.
Thanks
Jeff
--
Jeff
|