Substitute a numeric value for a text value in a calculation.

J

Jeff

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)+(G37*$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
 
D

dlw

You basicall have to use the IF function somewhere, not sure why yours did
not work, it looks OK. Anyway, instead of putting the IF in the formula, you
could make a hidden column next to the score that has the IF test, then do
the calculations on that column.
 
T

T. Valko

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
 
J

Jeff

having problems making this work,

as I understand this it shouls work like this,

if F4 is NA then 5 else F4

when I tried it in my spread sheet it returned "N/A" and not "5"

Also how would you incorporate that into the larger formula?

Thanks
 
J

Jeff

Thanks for the idea, I thought of that and it would work but I am trying to
find something a little more elegant.

I will be sharing this with a large team and don't wnat to have a bunch of
support calls because they don't understand hidden column's. If I can find a
formula that will do this I can lock the cell and not have to worry about it
again. they they can just add columns as needed and not have to worry about
hidden cells.


Thanks
 
J

Jeff

example was just that, I could have been clearer, let me restate the formula


=(((C33*$B33)+(C34*$B34)+(C35*$B35)+(C36*$B36)+(C37*$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
 
T

T. Valko

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
 
J

Jeff

not quite there,

here is a snip from my worksheet,

Weight Score Score
Test Requirements 255.00 205.00
1 10 5 N/A
2 10 5 5
3 5 5 5
4 8 5 5
5 8 5 5
6 6 5 5
7 4 5 5

As you can see the N/A is not being figured into the total as it should be
the same as the previous column.

I am using your suggested formula,
=(SUMPRODUCT($B4:$B10,C4:C10)+COUNTIF(C5:C10,"N/A")*5)


is it just adding 5 as opposed to mulitplying the value 5 by the weight, in
other words,
am I getting total + 5 or total + (5 * Weight)

Thanks

Jeff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top