I want to assign a value to the letter R in a cell range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?
 
Poke said:
Actually it's not as simple as what's written in the subject line. I want to
have the following happen; If I enter the letter "r" in a specified cell
range then I want a numeric value of 8 to appear in the total column cell
range for that line. However if I enter "R+1" then it should read as 9 "R+2"
10 etc... here's an example of what I'm trying to acheive,

Name Mon Tues Wed.... Total
Bob Doug R+2 R-1 R 25
Tom David R-5 R+3 R+1 23

Any suggestions?
Click to expand...

If you could use a letter other than "r" or "c", then you could
Insert/Name/Define.... Then a letter (say, Q) and in the "refers to"
blank, put in =8.

then in your cells, you would put in =Q+1 or Q+2 and get the answer 9 or 10

Beege
 
Unfortunately the Text value must be R and be able to have a total drawn from
it at the end column for each row.

Thank you for your quick response though
 
if the R will always be the first character
and nothing would be entered without an R
if Mon were column B
in totals cell enter
=if(len(B2)>0, if
len(B2)=1,8,8+value(right(b2,len(b2)-1))+if(len(C2......{for the week}
Similar equations can be used with more complex combinations
 
The following ugly array formula works. In this example, the data is in B9:H9

=SUM(IF(ISERROR(FIND("R",UPPER(B9:H9))),0,8+IF(LEN(B9:H9)>1,INT(RIGHT(B9:H9,LEN(B9:H9)-1)),0)))

To enter an array formula, copy & paste the formula above into a cell. You
may have to adjust for the text wrapping above. Instead of Enter, you press
{Ctrl}{Shift}{Enter} together to enter the array formula. If you do it
correctly, Excel will display curly braces around the formula.

Hope this helps,

Hutch
 
Thanks to all that replied.

Tom you rock! This is exactly what I was looking for! It works perfectly. I
didn't know about the whole CTRL-SHIFT-ENTER aspect. I'm a little new to
excel. Gee can you tell? HA!

Buddy you're awesome!

Thanks
 
Back
Top