If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Getting text or numerical data from the formula
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Getting text or numerical data from the formula

#1
July 27th 12, 04:07 PM
 Bootface Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 4
Getting text or numerical data from the formula

I have column O which is date format 00/00/0000, column Q which has either the word 'Date' or a numerical figure to 1 decimal place and column I which has a numerical figure to 1 decimal place. I need a formula in column S that looks at Q and if it shows date then it needs to return N/A in the S cell and if it has the numerical figure to 1 decimal place it needs to do the sum Q-I and give the result in the S cell. What should my formula be? Thanks in advance for any help. :-)
#2
July 27th 12, 04:24 PM
 Spencer101 Senior Member First recorded activity by ExcelBanter: Mar 2012 Posts: 653

Quote:
 Originally Posted by Bootface I have column O which is date format 00/00/0000, column Q which has either the word 'Date' or a numerical figure to 1 decimal place and column I which has a numerical figure to 1 decimal place. I need a formula in column S that looks at Q and if it shows date then it needs to return N/A in the S cell and if it has the numerical figure to 1 decimal place it needs to do the sum Q-I and give the result in the S cell. What should my formula be? Thanks in advance for any help. :-)

S.
#3
July 27th 12, 09:57 PM posted to microsoft.public.excel.worksheet.functions
 zvkmpw external usenet poster Posts: 114
Getting text or numerical data from the formula

> I have column O which is date format 00/00/0000, column Q which has
> either the word 'Date' or a numerical figure to 1 decimal place and
> column I which has a numerical figure to 1 decimal place. I need
> formula in column S that looks at Q and if it shows date then it needs
> to return N/A in the S cell and if it has the numerical figure to 1
> decimal place it needs to do the sum Q-I and give the result in the
> cell. What should my formula be?

If the figures to 1 decimal place are already rounded, maybe this would work:
=IF(ISNUMBER(Q1), Q1-I1, "N/A")
Enter it it S1 and copy downward. If Q1 and I1 aren't rounded yet, apply the appropriate rounding function to them inside the formula.

Apparently, column O plays no role in the calculation.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post I need my formula to analyse numerical and text data Nightrunning Excel Discussion (Misc queries) 4 May 22nd 09 06:45 PM Numerical data formula Thyag Excel Worksheet Functions 5 August 14th 07 01:16 AM Text and Numerical data in a Pivottable without summarising? Madhouse Excel Discussion (Misc queries) 1 May 24th 06 02:09 PM Prevent non-numerical data (e.g. text) from plotting on a chart jaweiss Charts and Charting in Excel 1 March 19th 05 05:54 PM Numerical Data vs Text Jim Hollis Excel Programming 11 January 4th 04 06:27 AM

All times are GMT +1. The time now is 02:04 AM.