Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
John Sayaff
 
Posts: n/a
Default how can I subtract excel cell values that contain both a number a.

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: how can I subtract excel cell values that contain both a number a.

Yes, you can achieve this by using the following steps:
  1. Select the cell where you want to display the result of the subtraction.
  2. Type the formula
    Code:
    =VALUE(cell1)-VALUE(cell2)
    where "cell1" and "cell2" are the cells containing the values you want to subtract.
  3. Press Enter to calculate the result.

The VALUE function in Excel converts a text string that represents a number to a numeric value. By using this function, you can extract the numeric value from the cells that contain both a number and a letter.

For example, if you want to subtract the values in cells A1 and B1, you would type
Code:
=VALUE(A1)-VALUE(B1)
in the cell where you want to display the result.

After you press Enter, Excel will calculate the result of the subtraction and display it in the selected cell. If you want to display the result with the units, you can simply type them after the formula in the same cell.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Peter Rooney
 
Posts: n/a
Default

John,

Assuming there is a space between the value and the suffix, you could strip
the numeric value out of a cell this:

=LEFT(M30,FIND(" ",M30))

This will return all the characters in the string in M30 up to the space -
this should be your numbers. You could then use this idea to subtract the
numeric elements of two cells from each other thus:

=LEFT(M30,FIND(" ",M30))-LEFT(M31,FIND(" ",M31))

Hope this helps - unledd there isn't a space between the number and the
suffix, in which case I'll have to think again!

Cheers

Pete







"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

  #4   Report Post  
Peter Rooney
 
Posts: n/a
Default

John,

my last answer, just to be sure, you need to subtract 1 when you've
found your space - thuis, if the space occurs at the fourth position, you
only want to see the first 3 characters, and so on. This version also adds
the appropriate suffix onto the end of your formula. Wish I'd thought of it
first time round!

=LEFT(M30,FIND(" ",M30)-1)-LEFT(M31,FIND(" ",M31)-1)&" Mhz"

Pete


"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

  #5   Report Post  
Peter Rooney
 
Posts: n/a
Default

John,

This was a GOOD problem!

This final version strips the suffix from M30 and adds it, prefixed by a
space, to your answer.

=LEFT(M30,FIND(" ",M30)-1)-LEFT(M31,FIND(" ",M31)-1)&"
"&RIGHT(M30,LEN(M30)-FIND(" ",M30))

I'll go away now.

Pete



"John Sayaff" wrote:

I have measurement data from an ATE system where the results are given as
both a number and 'unit' letters for example 22.3 dB, 2.109 MHz, 12.06 V and
0.175 A. To calculate temperature drift from these readings, I need to
subtract cells that contain both a number and a letter i.e.

2.012 MHz - 2.005 MHz = 0.007
does not matter if the answer contains the 'units' or not.

anyone know how I can achieve this ?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Toggle multiple values in single cell Chandni Excel Worksheet Functions 5 February 10th 05 01:48 AM
In MS Excel, how do I fill in a column with the same cell from se. krempin Excel Worksheet Functions 2 February 9th 05 09:43 PM
Can an excel cell automatically change fill colors based on values John Clark Excel Discussion (Misc queries) 1 February 5th 05 06:21 PM
Can you reference cell values in Headers and Footers in Excel 200. jkyte Excel Discussion (Misc queries) 2 December 30th 04 10:05 PM
how do i set up a single cell continual entry in excel to total f. mike@swallow Excel Discussion (Misc queries) 1 December 7th 04 01:29 PM


All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"