snofamthrlsgoat
Level 1 (25 points)
iWork

Q: Formula, strings = number values?

is there a way in Numbers to have a cell display a string, but when that cell is referenced in a formula it will be used as a numeric value? see screen shot.

i want the Storage to be the label for Capacity.

i want the strings displayed in column A to represent the numeric values in column B.

so "128 GB" = 0.13. or "128 GB" = B2. i need one cell to associate with the value of the other.

 

Screen Shot 2016-08-22 at 17.15.50.png

 

my goal is to make a column of pop-ups with storage capacities that can be used in a formula in a different table.

if "128 GB" is chosen i want its Actual value to be 0.13

iWork, OS X El Capitan (10.11.6)

Posted on Aug 22, 2016 5:45 PM

by Wayne Contello,Solvedanswer
Wayne Contello Wayne Contello
Level 6 (19,421 points)
iWork
A:

I prefer to use a lookup table like this.

 

Screen Shot 2016-08-22 at 8.33.09 PM.png

 

1) create a table (like what you have) that contains the list of text and corresponding values

I named the table "SizeTo Value"

 

In the table where you would like to use this, use the following formula:

C2=IFERROR(IF(VLOOKUP(B2, SizeTo Value::A:B, 2, 0)=0, "", VLOOKUP(B2, SizeTo Value::A:B, 2, 0)), "")

 

this is shorthand for... select cell C2, then type (or copy and paste from here) the formula:

=IFERROR(IF(VLOOKUP(B2, SizeTo Value::A:B, 2, 0)=0, "", VLOOKUP(B2, SizeTo Value::A:B, 2, 0)), "")

 

select cell C2, copy

select cells C2 thru the end of column C, paste (or as needed)

Posted on Aug 22, 2016 7:21 PM

Close
snofamthrlsgoat

Q: Formula, strings = number values?

  • All replies
  • Helpful answers

  • by Wayne Contello,Solvedanswer

    Wayne Contello Wayne Contello Aug 22, 2016 7:21 PM in response to snofamthrlsgoat
    Level 6 (19,421 points)
    iWork
    Aug 22, 2016 7:21 PM in response to snofamthrlsgoat

    I prefer to use a lookup table like this.

     

    Screen Shot 2016-08-22 at 8.33.09 PM.png

     

    1) create a table (like what you have) that contains the list of text and corresponding values

    I named the table "SizeTo Value"

     

    In the table where you would like to use this, use the following formula:

    C2=IFERROR(IF(VLOOKUP(B2, SizeTo Value::A:B, 2, 0)=0, "", VLOOKUP(B2, SizeTo Value::A:B, 2, 0)), "")

     

    this is shorthand for... select cell C2, then type (or copy and paste from here) the formula:

    =IFERROR(IF(VLOOKUP(B2, SizeTo Value::A:B, 2, 0)=0, "", VLOOKUP(B2, SizeTo Value::A:B, 2, 0)), "")

     

    select cell C2, copy

    select cells C2 thru the end of column C, paste (or as needed)

  • by snofamthrlsgoat,

    snofamthrlsgoat snofamthrlsgoat Aug 22, 2016 7:23 PM in response to Wayne Contello
    Level 1 (25 points)
    iWork
    Aug 22, 2016 7:23 PM in response to Wayne Contello

    this is great thanks!

    being able to see VLOOKUP used in this context is exactly what i needed.

  • by SGIII,

    SGIII SGIII Aug 22, 2016 7:27 PM in response to snofamthrlsgoat
    Level 6 (10,796 points)
    Mac OS X
    Aug 22, 2016 7:27 PM in response to snofamthrlsgoat

    If you like things compact and don't mind a more complicated formula you can do the conversion from the choice in your Pop-Up Menu to a numerical value with something like this:

     

    Screen Shot 2016-08-22 at 10.17.51 PM.png

     

    The formula is entered as:

     

      =IF(RIGHT(A,2)="TB",LEFT(A,FIND(" ",A,1)−1)*1,LEFT(A,FIND(" ",A,1)−1)/1000)

     

    In the formula editor it looks like this:

     

    Screen Shot 2016-08-22 at 10.19.42 PM.png

     

    It looks for the position of the blank and uses that to parse the string, multiplying the "numeric" part before the blank by 1 if it finds "TB" at the end, dividing it by 1000 otherwise (i.e. there is "GB" there).

     

    The rows below row 2 weren't necessary, just used for testing (i.e., no need for a lookup table as it all fits in one cell).

     

    SG