Is it possible to display a character, but utilize a value in the same cell?

  • Thread starter Thread starter G.T.W.
  • Start date Start date
G

G.T.W.

I am working on a schedule of inspections.
I have a column that displays either a Q(Quarterly), S(Semi-Annual) or
A(Annual) . I'd like to be able to link the column to an equation
with the values 4, 2, or 1. (IE Quarterly is 4 inspections a year)
Can a "Q" be displayed in a cell, but use a math value of 4 as part of
an equation for further calculations
For Example:

Cell A I Cell B I Cell C I Cell D
Qty I Insp I Hours I Total
2 Q .75

(2 x 4 x .75) = 6 inspection hours Annually


I'd like the sheet to display the type of inspection, for clarity, but
also want that cell to function as part of the equation. Is this
possible? Thank you.
 
Yes, you could use something like

=IF(B2="","",A2*VLOOKUP(B2,{"A",1;"S",2;"Q",4},2,0)*C2)

where B is the column with type of inspection. If B is empty total will be
empty as well
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help! How to give a drop down cell a math value? 1
Ignore text but count values in same cell 2
Interpolate data 2
I want the autofill/drag function to skip empty cells 1
Formula - Is it possible to... 3
Calculate the value of Z 12
Increment and replace a cell value 1
Can I search for the displayed value in a cell, not the contents? 2

Back
Top