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

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
didnt work, not sure why.

Thanks

Jeff

--
Jeff

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

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

Vaya con Dios,
Chuck, CABGx3



"Jeff" wrote:

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
didnt work, not sure why.

Thanks

Jeff

--
Jeff

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

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.

"Jeff" wrote:

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
didnt work, not sure why.

Thanks

Jeff

--
Jeff

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

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

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

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


"CLR" wrote:

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

Vaya con Dios,
Chuck, CABGx3



"Jeff" wrote:

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
didnt work, not sure why.

Thanks

Jeff

--
Jeff

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

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


"dlw" wrote:

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.

"Jeff" wrote:

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
didnt work, not sure why.

Thanks

Jeff

--
Jeff

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

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

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

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

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

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

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

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


"T. Valko" wrote:

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

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

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

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