Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 (110 ) 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 (110 ) 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 (110 ) 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 (110 ) 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 (110 ) 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numeric calculation | Excel Worksheet Functions | |||
How do I substitute text for numbers | Excel Discussion (Misc queries) | |||
Substitute text with assigned values | Excel Discussion (Misc queries) | |||
how to substitute a value for calculation with a column of values | Excel Worksheet Functions | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) |