If functions and blank cells

Version: 2008
Operating System: Mac OS X 10.3 (Panther)
Processor: Intel

I have 2 columns of data with measurements that need to be converted. One column is in inches, the other is in centimeters. The bulk of the measurements are in inches, which means that most of the blank cells are in the centimeters column. However, a few of the centimeter cells have measurements. <br><br>How do I set up a conversion formula in the centimeter column without wiping out the information already there? <br><br>Basically I want to set it up to say something like: if f2 is blank then use value in g2, if f2 is Y, then multiply Yx2.54  <br><br>Thank you for any help you can give.
0
Ldsoldier
5/15/2010 9:13:15 PM
mac.office.excel 1146 articles. 0 followers. Follow

4 Replies
1203 Views

Similar Articles

[PageSpeed] 42

On 5/15/10 5:13 PM, Ldsoldier@officeformac.com wrote:
> Version: 2008 Operating System: Mac OS X 10.3 (Panther) Processor: Intel
> I have 2 columns of data with measurements that need to be converted.
> One column is in inches, the other is in centimeters. The bulk of the
> measurements are in inches, which means that most of the blank cells are
> in the centimeters column. However, a few of the centimeter cells have
> measurements.
>
> How do I set up a conversion formula in the centimeter column without
> wiping out the information already there?
>
> Basically I want to set it up to say something like: if f2 is blank then
> use value in g2, if f2 is Y, then multiply Yx2.54
>
> Thank you for any help you can give.
=if(len(f2)=0,g2,f2*2.54)

-- 

Bobgreenblattatmsndotcom
0
Bob
5/15/2010 11:47:19 PM
Thank you. Now, how did you come up with that?
0
Ldsoldier
5/16/2010 12:21:51 AM
On 5/15/10 8:21 PM, Ldsoldier@officeformac.com wrote:
> Thank you. Now, how did you come up with that?
Just comes naturally as a result of working with excel every day all day 
since 1985.

-- 

Bobgreenblattatmsndotcom
0
Bob
5/16/2010 12:51:32 AM
I can atest to Bob being a genius. :-)

Bob Greenblatt wrote:
> On 5/15/10 8:21 PM, Ldsoldier@officeformac.com wrote:
>> Thank you. Now, how did you come up with that?
> Just comes naturally as a result of working with excel every day all day
> since 1985.
>

-- 
Phillip M. Jones, C.E.T.        "If it's Fixed, Don't Break it"
http://www.phillipmjones.net        mailto:pjones1@kimbanet.com
0
Phillip
5/16/2010 2:48:48 AM
Reply:

Similar Artilces:

Preventing Duplicate Entries Across Cells
Anyone know how to prevent duplicate entries of numbers. e.g. I have two sheets. I want to make sure that the same number is not entered more than once. Thank you. I think this might work- Have a countif funtion (of pair of for 2 sheets) that look a th current row and see if it has duplicate entries ie a b 1 1 =COUNTIF($A$1:$A$2,A1) - 1 2 2 =COUNTIF($A$1:$A$2,A2) - 1 (the minus one has been added so when a number exists only once it wil return 0. An if statement may also be need for if hte cell is blank.) Then add this behi...

hey ya'll how do i auto sum a cell using the letter x instaed of .
hey ya'll how do i auto sum a colum using the letter x instead of using a number To count number of "x"es =COUNTIF(A1:A100,"x") Gord Dibben Excel MVP On Mon, 25 Oct 2004 14:25:04 -0700, jrod98 <jrod98@discussions.microsoft.com> wrote: >hey ya'll how do i auto sum a colum using the letter x instead of using a >number Gord answered but I couldn't resist asking what the sum of x and x is? Sinister Rod Serling music here. -- Don Guillett SalesAid Software donaldb@281.com "jrod98" <jrod98@discussions.microsoft.com> wrote in mes...

paste text into a used cell
I need to move text from one row to a single cell that has been formatted to wrap text and repeat the process for other rows. Paste is not working. Please advise. When you copy and paste, you paste the format as well as the contents If you want to copy text to a cell that is formatted differently instead of paste, slelct paste special, values -- ElsiePO ----------------------------------------------------------------------- ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390 View this thread: http://www.excelforum.com/showthread.php?threadid=26962 ...

How do I display html code within a cell?
I am importing data to a cell. The data is actually html code. I would like to display the data formatted. Right now I get all the ugly html codes, like <b> Hello World</>. Thanks - John You need to use Insert>Function and then select Hyperlink from the Lookup & Reference category. Randall Arnold "John-Raritan" wrote: > I am importing data to a cell. The data is actually html code. I would like > to display the data formatted. Right now I get all the ugly html codes, like > <b> Hello World</>. > > Thanks - John I think I...

Error:Object expected , Function:setFocus()
Hi, I am getting a pop up msg. box while saving an activity or incident with values in any lookup fields(ex: From, To ,Regarding frields in Activity). This start happening after UR7 upgrade. when I keep all lookup fields empty and save it's successfull. Please let me know if anybody faced the similar error and got resolved. Error details I am seeing when clicking on 'View the date that will be sent to Microsoft' is, Microsoft Dynamics CRM Error Report Contents <CrmScriptErrorReport> <ReportVersion>1.0</ReportVersion> <ScriptErrorDetails> <Mess...

IIF Function error in Access 2003
Hi, Following on from my separate post yesterday regarding issues I'm having after creating a database in Access 2007 which some users need to use from Access 2003. I have the following IIF function working perfectly in a form in Access 2007 which is now returning a #Name? error in 2003 when the same form/database is opened with 2003 and I have no idea why? =IIf([ContractEndDate] Between Date() And Date()+90,"To Be Renewed",IIf([ContractEndDate]>Date()+90,"In Contract",IIf([ContractEndDate]<Date(),"Contract Expired","Start/End D...

Changing individual characters in a cell to a different character
I am trying to write a bit of code that will look at a cell and covert what's in the cell to some sort of code. e.g. Hello Goodbye Yes No The above words are in different cells, and need converting to a different code, for example H is 1, e is 2, l is 3 and 0 is 4, so Hello would be 12334 The entries to the cells will be different, so I can't just do a select case and have Hello as one of them. Is there any way of doing a select case type statement, but for it to look at individual characters in a cell and cycle through until it's finished and then move onto the next c...

filling cells with info from other file (unique id)
I like to know if its possible to get information from an excelfile and put this in another excelfile using a unique id-number. When I fill a cell in A-column with for example 12345, I want excel (or vbamacro?) to look in another file (xls\txt, or?) for 12345 and transfer the rowvalues to the corresponding columns in the worksheet I started with. Can anyone put me in the right direction how to do this? Is it some standard functionallityu of excel or is it something I have to program? Thnx --- Message posted from http://www.ExcelForum.com/ You could use a VLOOKUP formula to extract data...

Data Validation / Cell Entry
Hi Is there a way I can force a user to make an entry in a cell without using macros/code? EG: If the result of cell A1 = "XYZ" can I force them to make an entry in cell B1. ( If cell A1 = "" then no entry is required in B1). Hope that makes sense. Thanks Steve This won't force the user, but it will make it obvious. Format C1 in nice bold red letters and put a formula in that cell like: =if(and(a1="",b1<>""),"Please clear B1",if(a1="xyz",b1=""), "please put something in B1","") (...

VBA selecting an input range of cells
Hi I have a small request for help with code. I need a simple macro to have popups request the first and last cells in a range , and then to select the range indicated in the popups. Can someone help? Grateful for any assistance Something like: Sub PickaRanage() Dim s1 As String, s2 As String, s3 As String s1 = Application.InputBox(prompt:="Enter address of first cell", Type:=2) s2 = Application.InputBox(prompt:="Enter address of last cell", Type:=2) Range(s1 & ":" & s2).Select End Sub -- Gary''s Student - gsnu201001 ...

Filename in Excel Cell Address
Is there a way to insert an excel spreadsheet filename into a cell within the same spreadsheet, like the &[File] command in the Header/Footer screen? -- Thanks, RickS yes =CELL("filename") -- Gary Keramidas Excel 2003 "RickS" <RickS@discussions.microsoft.com> wrote in message news:A801EEC7-608B-4394-B991-BBBC889D451A@microsoft.com... > Is there a way to insert an excel spreadsheet filename into a cell within > the > same spreadsheet, like the &[File] command in the Header/Footer screen? > -- > Thanks, > Ric...

Survival distribution function chart
Is anyone familiar with the Kaplan-Meier estimator, and if there is a way I can get excel to draw a traditional survivior curve? The charts on this page http://www.xlstat.com/demoKM.xls are the style that I would like to produce. However, these appear to have been produced using an add-on called XLstat Pro. I am attempting to produce a spreadsheet to analyse various statistical models which are all reliant on producing a survivor curve in this style. Any help anyone could offer me would be gratefully received as I have spent days trying and can not find a way of doing this. -- max0d --...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

Trying to delete info in a cell
I have 1 column of information with over a thousand rows of info. I want to just delete some info in the cell and keep the rest. Anyone have any idea how to do this? Here's an example of my info: AABERG 0.000 83.839 38738 AADLAND 0.000 87.245 58868 AAGAARD 0.000 85.690 48524 I want to delete all the numbers. Ay help woud be greatly appreciated. ASAP Utilities, a free Add-in available at www.asap-utilities.com has a built in feature that will strip out all numerical characters from a string. hth Vaya con Dios, Chuck, CABGx3 "E2K3Tek" wrote: >...

Text Not Showing in Cell
I'm using excel to produce reports with almost all text. Some cells have a lot of words. Text Wrap is on. Row Height is set to AutoFit. Most cells show up just fine. Some have hidden words that run on under the next cell below. I have even encountered some cells that cut off right through the middle of the text. I can, of course, manually change the height of each row, but this report is over 200 lines long. Same problem happens when printed. Please help. Excel is not the greatest text editor. Have you considered doing the report in Word, using the Tables function? You co...

insert blank row every 10 rows
Is it possible to insert a blank row every 10th row starting from row 11 and going until the last and variable row of data? maybe this? Sub test() Dim ws As Worksheet Dim lastrow As Long Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row Do Until lastrow < 11 If lastrow Mod 10 = 0 Then ws.Rows(lastrow).Offset(-9).EntireRow.Insert End If lastrow = lastrow - 1 Loop End Sub -- Gary Keramidas Excel 2003 "aileen" &...

Automatic changes in cell entries
How does one shut off the abjectly rediculous habit of Excel insisting on knowing better what I want for cell entries than I do? EG If I type AKs I want AKs not Aks... :mad: I have reviewed all the options in Tools|Options as well as other places to no avail. TIA tools-option-edit and the last item <enable autocomplete cell values> uncheck this doco <perdedor@centurytel.net> wrote in message news:#LhMjTA4EHA.3472@TK2MSFTNGP09.phx.gbl... > How does one shut off the abjectly rediculous habit of Excel insisting on > knowing better what I want for cell entries than I ...

Inserting blank rows plus using Sum
I have a list of stocks sorted by date. When the stock symbol changes, I need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500...

If functions??
Im trying to create a spreadsheet file where I can input data into a column and have it insert a certain factor. For example I want to be able to input data so that if i type 70 in one column it enters .985 in the next column and if i enter 71 then 979 is entered that column and so forth. The numbers are part of a temperature correction factor for a lab test so they range from 60 to 89 degrees therefore there are about 30 if then combinations. I tried visual basic but got lost can anyone help me out with a code or point me in the right direction?? Thanks -- DBassmaker -----------------...

Lock cell dimensions
After I have adjusted the row and column dimensions to just the way that I want them, can I lock them so that they will be the same next time it is loaded/opened? Thanks Brian Tozer "Brian Tozer" <briantoz@ihug.co.nz> wrote in message news:bs971s$vkb$1@lust.ihug.co.nz... > After I have adjusted the row and column dimensions to just the way that I > want them, can I lock them so that they will be the same next time it is > loaded/opened? > > Thanks > Brian Tozer > > Why do you need to 'lock' them? When you close a workbook and open it again, ...

Cant merge cell
When I merge two cells , the contents of which are X and Y, I get only one X in the merged cell. Worthless... I would like to get XY This is not possible in Excel, but if you wish you can use this formula in another cell, If X is in cell A1 and Y is in cell B1, you can write this formula in any cell: =CONCATENATE(A1,B1) I am no expert, maybe somebody else can come up with a better solution Regards "Dadada" wrote: > When I merge two cells , the contents of which are X and Y, I get only one X > in the merged cell. Worthless... > I would like to ge...

"" is an empty cell?
Dear All If I insert ="" in cell A1 and =2 in cell B1, then when I insert =A1*B1 in cell C1, I do not get 0. Does not "" correspond to an empty cell? Thanks in advance, Paul No it doesn't. "" in a cell is a null string which means it isn't empty but contains "". You're formula =A1*B1 is multiplying "" by 2 which is effectively multiplying a text value by 2, so it will return #VALUE! Regards, Alan "Paul Smith" <phhs80@gmail.com> wrote in message news:1159970351.191296.70950@i3g2000cwc.googlegroups.com......

Autocomplete Custom Functions and functions arguments
Hi, I have custom functions in VBA. I want Excel to show the Autocomplete options as the Microsoft Office Excel displays below the cell a dynamic drop-down list of valid functions, names, and text strings that match the letters or trigger. Also, I want to make the arguments be shown automatically For example, to make clear what i want On pressing =sum for example ( excel automatically shows Sum(Number1, [Number2],...) to help the user on the arguments So, I want my custom function also show the same. Thanks for your reply to may question and the response. On May 20, 5:15=A0pm, ...

Cell Issue
I have a cell in excel 2007 and it contains a paragraph but when you click out of the cell it looks like "############################################################" How do I get it to look like the data that in the cell? Hendrix brought next idea : > I have a cell in excel 2007 and it contains a paragraph but when you > click out of the cell it looks like > "############################################################" > > How do I get it to look like the data that in the cell? The contents of the cell are more than what can display in the cell at its ...

Changing properties with a function
Hi all, I'm trying to change the colour and special effect of a rectangle through a function because I want to have stop and go lights and I want to be changing them frequently, so my function is like this: Public Function SwitchOn (BoxName) as String Form_Home.BoxName.BackColor.Value = 4634122 Form_Home.BoxName.SpecialEffect.Value = "Sunken" end function then I call it by using: switchon (box1) - also tried switchon ("box1") but it doesn't want to work at all... how do I get the damn thing to work???? Do I have to set BoxName as a Rectangle or an Object ins...