Simple Formula Question

motzstev

New Member
Joined
Dec 6, 2005
Messages
2
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>



</p>


<span class="largetext">I have 3 columns in a spreadsheet and I want to write
a simple formula or script that will allow me to create the new columb given the
values in column 1, 2, and 3.



</span></p>
<table>
<col width="90" style="mso-width-source:userset;mso-width-alt:3214;width:68pt">
<col width="64" span="2" style="width:48pt">
<col width="64" span="2" style="width:48pt">
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" width="90" style="height:13.2pt;width:68pt">A</td>
<td class="xl22" width="64" style="width:48pt">B</td>
<td class="xl22" width="64" style="width:48pt">C</td>
<td width="64" style="width:48pt"></td>
<td class="xl22" width="64" style="width:48pt">New Column</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt" x:num>11002</td>
<td class="xl22" x:num>11001</td>
<td class="xl22"></td>
<td></td>
<td align="right" x:num>11002</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt" x:num>32322</td>
<td class="xl22" x:num>21221</td>
<td class="xl22" x:num>11011</td>
<td></td>
<td class="xl23" x:num>
<p align="right">32322</p>
</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt" x:num>12133</td>
<td class="xl22"></td>
<td class="xl22"></td>
<td></td>
<td align="right" x:num>12133</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt"></td>
<td class="xl22" x:num>43233</td>
<td class="xl22" x:num>44332</td>
<td></td>
<td align="right" x:num>43233</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt"></td>
<td class="xl22"></td>
<td class="xl22" x:num>12112</td>
<td></td>
<td align="right" x:num>12112</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt"></td>
<td class="xl22" x:num>43222</td>
<td class="xl22"></td>
<td></td>
<td align="right" x:num>43222</td>
</tr>
<tr height="18" style="height:13.2pt">
<td height="18" class="xl22" style="height:13.2pt"></td>
<td class="xl22"></td>
<td class="xl22" x:num>11212</td>
<td></td>
<td align="right" x:num>11212</td>
</tr>
</table>


Is there any way for the new column to look at columns 1,2,3 and give the
value of column 1 if there is anything present, if not look at column 2, etc....



Thank you very much for any help



Steve</p>

</body>

</html>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

In D1 try

=IF(A1,A1,IF(B1,B1,C1))

This does assume that the data will be numeric, and the output will be non zero.

If this is not the case, then please give examples of the data that will be available.


Tony
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Welcome to the board!

Like this?:

Edit:
Book8
ABCDE
1323122543323
2233433233
3433433
4 
5
Sheet2


If any of the three cells has a non-value blank is returned.

Hope This Helps.

RAM
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Good point acw,

This will return numeric as well as text values based on my previous post:

Edit:
Book8
ABCDE
1JK122543JK
2233PL233
3433433
40
5
Sheet2


Copy and paste this formula in D1: =IF(OR(ISTEXT(A1),ISNUMBER(A1)),A1,IF(OR(ISTEXT(B1),ISNUMBER(B1)),B1,C1)) and copy down.

RAM
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

A but more generic

=IF(NOT(ISBLANK(A1)),A1,IF(NOT(ISBLANK(B1)),B1,C1))

Tony
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Tony's steps ahead of me as usual, but thanks, I learned too. Not and Isblank, didn't think about that.

RAM
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Or possibly like this if you want the cell to return blank and not 0 if all cells are blank:

=IF(NOT(ISBLANK(A1)),A1,IF(NOT(ISBLANK(B1)),B1,IF(NOT(ISBLANK(C1)),C1,"")))

HTH

RAM

PS. Copy and Paste the formula and use Insert Function (fx) next to the Formula Bar and walk through the formula to see how it works.

PS. Next time when you're starting from scratch use Insert Function to save yourself a lot a headache and build formulas with that tool.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top