If function

[Loudfish]

I am a noobie at excel trying to solve an if function issue. I want to have the referenced cell use a formula if nothing is entered, but if I type in a number (to that cell) I want that to be displayed.

I.E... I know the answer to 2+2 so I want to enter 4 into Cell B6 (to have that displayed). If I do not have the answer to 2+2, I want Cell B6 to reference another cell that uses a formula to derive the answer.

 

[Greg Truby]

Welcome to the board. You can't do what you want without VBA and if you're just trying to wrap yer noggin around IF() functions, probably ain't ready for macros, let alone event-driven macros.

But if you're going to have a formula in the cell, then why are you overwriting it with a value?

 

[Loudfish]

Thanks for the response.

I work in the electrical industry and sometimes cut sheets will give a value in % per °C or mv °C. I need to convert to mv for a separate table I have created, when value is in %. So if the cut sheet has the mv listed I just want to enter that instead of referencing the formula. This way, in the seperate table, I can just reference the cell and it will always display the value in mv.

 

[Greg Truby]

The way to do that is use one set of cells for data entry and another for calculation or output. Then you can drop flags into the input cells that the output cells will use.

Example - key in a number in the input cells (column A) and the output (column B) does not change the value. But key a number and the letters "mv" into the input cell and the output cell removes the "mv" and multiplies by 100.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">44mv</TD><TD style="TEXT-ALIGN: right">4400</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">34 mv</TD><TD style="TEXT-ALIGN: right">3400</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(LEN(SUBSTITUTE(A1,"mv","")) = LEN(A1),A1,SUBSTITUTE(A1,"mv","") * 100)</TD></TR><TR><TD>B2</TD><TD>=IF(LEN(SUBSTITUTE(A2,"mv","")) = LEN(A2),A2,SUBSTITUTE(A2,"mv","") * 100)</TD></TR><TR><TD>B3</TD><TD>=IF(LEN(SUBSTITUTE(A3,"mv","")) = LEN(A3),A3,SUBSTITUTE(A3,"mv","") * 100)</TD></TR><TR><TD>B4</TD><TD>=IF(LEN(SUBSTITUTE(A4,"mv","")) = LEN(A4),A4,SUBSTITUTE(A4,"mv","") * 100)</TD></TR><TR><TD>B5</TD><TD>=IF(LEN(SUBSTITUTE(A5,"mv","")) = LEN(A5),A5,SUBSTITUTE(A5,"mv","") * 100)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

An alternate formula would be:

=IF(ISNUMBER(FIND("mv",A1)),SUBSTITUTE(A1,"mv","") * 100,A1)

And don't forget that you can use the Evaluate Formula tool to see what's going on inside the cell as it evaluates the formula.

 

[Loudfish]

Thank You, but not exactly what I was looking for .

If I need to convert, VOC, to volts/°C using a %/°C , I have a formula entered into L6 that is L2*X. If I already have the VOC in V, then I want to just enter in that number into L6. I am then referencing that cell in an equation located in another table (that needs the input to be in V not %).