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

[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

 

[CLR]

=IF(ISNA(F4),5,F4)

Vaya con Dios,
Chuck, CABGx3

 

[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. 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

 

[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

 

[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

 

[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. Valko]

Ok...try this:

=SUMPRODUCT(B33:B39,C33:C39)+COUNTIF(C33:C39,"N/A")*5

Biff

 

[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

 

[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

 

[Jeff]

That's the ticket, exactly what I was looking for, thank you very much.

 

[T. Valko]

Good deal. Glad we got it straightened out!

Biff