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

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. 
0
rampart (6)
10/25/2003 10:22:16 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
743 Views

Similar Articles

[PageSpeed] 4

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


-- 

Regards,

Peo Sjoblom

"G.T.W." <rampart@easynews.com> wrote in message
news:3f9af3e8.288560@news.ev1.net...
> 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.


0
terre081 (3244)
10/25/2003 10:51:35 PM
Reply:

Similar Artilces:

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

unusual characters
I've got an excel file that contains several unusual characters. These look like square boxes, and they screw up some code that I've written to process my data. I can't do a find and replace because I don't know what the character is and I can't paste it. If I paste it into notepad, it looks like 2 double quotation marks on seperate lines! Any ideas on what these are and how to get rid of them? Thanks, George Try edit>replace, in the find what box hold down alt while typing 010 on the numpad, release alt if you want to replace them with a space type a space in the...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Intermittent display
Hi, recently i encountered this problem on my PC. When i press the ON button, the monitor shows the normal starting up image for only 1 second and then it went black. Then, because my computer has a CPU FAN error (probably because the starting speed of my fan is a little slow), it would beep 3times and prompt me to press F1. During this time, the image would appear on the monitor for 1sec and then, becomes black again. Then, as it loads and enters window, it will flash yet again and i can see the cursor of my mouse at the center of the screen for that 1 second. Everything is running normall...

display name #2
hi i have this problem: when i send a mail massage from an application by exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and not display name (Customer Care) how can i resolve it? Thanks An application should allow you to specify display name for sender. Does it send via SMTP or MAPI? Matrix_pd wrote: > hi > i have this problem: when i send a mail massage from an application by > exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and > not display name (Customer Care) > > how can i resolve it? > > Thanks -- Please re...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Excel formulation to automate values
Hi there, I have a little problem on arranging a small multi-worksheet excel here. Let me explain in a short way then give some details on it. I am trying to make an offers recordsheet which has two worksheets in it. One for products(and their base prices) and an offer sheet. I would like to use a bit dynamic data here. So when I type the name of the product excel would give me the price from other worksheet. in details; Prices worksheet is something like: A B 1 Product BasePrice 2 mouse 5 3 keyboard 8 4 ... And the ...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

Excel not displaying German characters correctly.
This is a real puzzler. Has our entire office and IT department stumped. We have two machines (M1 / M2) which are equal spec/ operating system, applications, versions, etc... The only difference that we can see between the two machines is that one (M1) has Internet Explorer encoding set to "UTF-8" with "Automatic" selected. The other (M2) has "Western European ISO" with no automatic selected. Now, when the machines log into a web application and choose to download a German Excel file, M1 and M2 get the file correctly if they have their encoding settings...

Outlook 2003 not displaying multipart/mixed messages properly
This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C49BFC.AB34E1D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I know that Outlook is known for having problems displaying = multipart/mixed messages but this is just weird. I am using Outlook 2003 with a POP email account and occasionally with = an email that has been exchanged a few times already and I will send out = a reply and when the person replies back to me what they wrote no longer = appears in the email. I basically get back a copy of the email I sent =...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

How to select other random cells
I have a 2 part question. I want to have a 1 question survey randomly filled out by 200 people. Column A lists the 1 questions. Columun b has ABCDE listed in cells B6 to b10. I would like 200 samples in columns c, d, e, etc. How do I set this up so that they randomly Coose B6 to B 10. Second part. On another spreadsheet, I have a similar situation, but the user has 10 answers to select from and I want them to randomly select all that apply. How would I do this? Thanks! ...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...

Excel to store history information and display a count of reschedules
Hi, I currently copy data from a system into an excel spreadsheet on a weekly basis, used to store a history of implementation records. For each record I store a unique ID, target date, owner, description, category, approval status and a result. Each week I create a new worksheet/tab with the date for the name. Each implemenation will take about 3 weeks and therefore the data will be repeated in about 3 worksheets, albeit with slight changes to approval status, target date or descriptions. What I want to do is to calculate, for each record (implementation), the amount of times the target dat...

I want to add scroll bar functionality in my screen capture utility
hi all, i have developed an application that shows a list of all running windows and allows user to capture their screens. I want to add scroll bar functionality in this app. I have add the scrollbar. scroll bars move but they dont scroll the image. please help me thanks & regards shoeb shoeb ali wrote: > hi all, > i have developed an application that shows a list of all running windows and > allows user to capture their screens. I want to add scroll bar functionality > in this app. I have add the scrollbar. scroll bars move but they dont scroll > the image. please ...

How do I call individual cell data from an Excel sheet into Powerpoint or Word?
Ok, I have an Excel work sheet done up to calculate discounts given to employees based on their employer. I now need to make a flyer in either Word or PowerPoint (or another program if needed) but I'd like for it to pull the data from individual cells in the Excel sheet. For instance in the midst of the graphics and flyer text that will remain the same for every flyer I'd like to be able to tell it to refer to Excel document, and then pull all the information for say, the company in row 7 (since 7 is the first company listed) It would need to pull the text (co. name) from A7, sug. r...

want to display number when = zero
Hello, I am creating a report but find problem in number display when the value = 0. When I've amended the format as "#,##0.00", it was automatically changed to "Standard" and displayed blank on report preview. Kindly please advise what should I do in order to display as 0.00 or 0 instead. Many thanks. Try this: #,##0.00;-#,##0.00;0;; -- Ken Snell <MS ACCESS MVP> "Mary" <abc@def.com> wrote in message news:e6$NPk%23iHHA.4552@TK2MSFTNGP04.phx.gbl... > Hello, I am creating a report but find problem in number display when the ...