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
 
Back
Top