Right most value minus left most value

M

mco5044

Board Regular
Joined
Jul 23, 2012
Messages
51
Hi,

I have done extensive searching for answers to this question and none of the solutions have been able to help me. Hopefully somebody here can :)

I am trying to create a formula to find the variance of a set of a numbers. I need to find the rightmost number in a row and subtract it from the leftmost number in the row. The twist is that the leftmost number may in some cases be a string of text, in which case I need to substitute the text string for the value 100.

Below is an example of what I am talking about. Hopefully it will make more sense when you see it.

So the end result for the bottom 3 rows should be 91, 99, and 91 respectively.

[TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl109, width: 52"]Jan[/TD]
[TD="class: xl110, width: 52"]Feb[/TD]
[TD="class: xl109, width: 52"]Mar[/TD]
[TD="class: xl109, width: 52"]Apr[/TD]
[TD="class: xl109, width: 52"]May[/TD]
[TD="class: xl109, width: 52"]Jun[/TD]
[TD="class: xl109, width: 52"]Jul[/TD]
[TD="class: xl109, width: 52"]Aug[/TD]
[TD="class: xl109, width: 52"]Sep[/TD]
[TD="class: xl109, width: 52"]Oct[/TD]
[TD="class: xl109, width: 52"]Nov[/TD]
[TD="class: xl109, width: 52"]Dec
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 624"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]not in top 100[/TD]
[TD]not in top 100[/TD]
[TD]81[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]not in top 100[/TD]
[TD]not in top 100[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]not in top 100[/TD]
[TD]not in top 100[/TD]
[TD]not in top 100[/TD]
[TD]20[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance to anyone who tries to take this problem on!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
mco5044 said:
Hi,

I have done extensive searching for answers to this question and none of the solutions have been able to help me. Hopefully somebody here can :)

I am trying to create a formula to find the variance of a set of a numbers. I need to find the rightmost number in a row and subtract it from the leftmost number in the row. The twist is that the leftmost number may in some cases be a string of text, in which case I need to substitute the text string for the value 100.

Below is an example of what I am talking about. Hopefully it will make more sense when you see it.

So the end result for the bottom 3 rows should be 91, 99, and 91 respectively.

[TABLE="width: 624"]
<TBODY>[TR]
[TD="class: xl109, width: 52"]Jan
[/TD]
[TD="class: xl110, width: 52"]Feb
[/TD]
[TD="class: xl109, width: 52"]Mar
[/TD]
[TD="class: xl109, width: 52"]Apr
[/TD]
[TD="class: xl109, width: 52"]May
[/TD]
[TD="class: xl109, width: 52"]Jun
[/TD]
[TD="class: xl109, width: 52"]Jul
[/TD]
[TD="class: xl109, width: 52"]Aug
[/TD]
[TD="class: xl109, width: 52"]Sep
[/TD]
[TD="class: xl109, width: 52"]Oct
[/TD]
[TD="class: xl109, width: 52"]Nov
[/TD]
[TD="class: xl109, width: 52"]Dec
[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 624"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD]not in top 100
[/TD]
[TD]not in top 100
[/TD]
[TD]81
[/TD]
[TD]10
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]not in top 100
[/TD]
[TD]not in top 100
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]not in top 100
[/TD]
[TD]not in top 100
[/TD]
[TD]not in top 100
[/TD]
[TD]20
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Thanks in advance to anyone who tries to take this problem on!
Click to expand...
Assuming there is always at least one number entered in a row.

Array entered**:

=IF(COUNT(B2:J2)>1,INDEX(B2:J2,MATCH(TRUE,ISNUMBER(B2:J2),0)),100)-LOOKUP(1E100,B2:J2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
T. Valko said:
Assuming there is always at least one number entered in a row.

Array entered**:

=IF(COUNT(B2:J2)>1,INDEX(B2:J2,MATCH(TRUE,ISNUMBER(B2:J2),0)),100)-LOOKUP(1E100,B2:J2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Click to expand...


Thank you very much for your quick response! The formula is much closer than I have been able to get so far, but it doesn't quite work yet.

It ignores the text "not in top 100" and takes the first number from the left instead of counting "not in top 100" as the number 100.

I think it's because the formula finds the first cell with a number in it instead of the first cell with a value in it. For example: on the first row of numbers I provided, the formula outputs 72 which is 81-9 instead of counting "not in top 100" as 100 and outputting 91.

Thanks again for your help, I really appreciate it
 
Upvote 0
mco5044 said:
Thank you very much for your quick response! The formula is much closer than I have been able to get so far, but it doesn't quite work yet.

It ignores the text "not in top 100" and takes the first number from the left instead of counting "not in top 100" as the number 100.

I think it's because the formula finds the first cell with a number in it instead of the first cell with a value in it. For example: on the first row of numbers I provided, the formula outputs 72 which is 81-9 instead of counting "not in top 100" as 100 and outputting 91.

Thanks again for your help, I really appreciate it
Click to expand...
What version of Excel are you using?
 
Upvote 0
mco5044 said:
Excel 2010. Does that actually make a difference in the formula? The logic would still be the same right?
Click to expand...
Depending on the Excel version, the formula might be made simpler. Like this...

Array entered**:

=IFERROR(--INDEX(A2:E2,MATCH(TRUE,A2:E2<>"",0)),100)-LOOKUP(1E100,A2:E2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes there will be at least one number in a row.
 
Upvote 0
T. Valko said:
Depending on the Excel version, the formula might be made simpler. Like this...

Array entered**:

=IFERROR(--INDEX(A2:E2,MATCH(TRUE,A2:E2<>"",0)),100)-LOOKUP(1E100,A2:E2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes there will be at least one number in a row.
Click to expand...


When I evaluate the formula the ISNUMBER portion always turns to N/A so it defaults to (100 - the last number)

I have tried playing with both of your formulas to see if I can get a solution but I still can't. This is what I think the logic needs to be:


IF(ISNUMBER(Find the first cell in the row with any value in it), use the number in that cell, 100)




Is there a way I can upload a spreadsheet for you to check out?
 
Upvote 0
A bit clunky, I'll try to improve it if it returnd the correct results
Code: 
=IF(ISNUMBER(INDEX($A2:$L2,,LOOKUP(FALSE,A2:L2<>"",COLUMN(A1:L1))+1)),INDEX($A2:$L2,,LOOKUP(FALSE,A2:L2<>"",COLUMN(A1:L1))+1),100)
-IF(ISNUMBER(LOOKUP(2,1/(A2:L2<>""),A2:L2)),LOOKUP(2,1/(A2:L2<>""),A2:L2),100)
 
Upvote 0
mco5044 said:
When I evaluate the formula the ISNUMBER portion always turns to N/A so it defaults to (100 - the last number)

I have tried playing with both of your formulas to see if I can get a solution but I still can't. This is what I think the logic needs to be:


IF(ISNUMBER(Find the first cell in the row with any value in it), use the number in that cell, 100)




Is there a way I can upload a spreadsheet for you to check out?
Click to expand...
You can't directly attach a file to this forum.

You can upload a file to some other site and then provide a link to that file in a reply. You can use a free file hosting site if need be.

If you can make up a SMALL sample file I'll take a look. Make sure you show what results you expect.
 
Upvote 0
Try this, it's a tad shorter.
Drag the formula in N2 Down as required.
Excel 2003
ABCDEFGHIJKLMN
JanFebMarAprMayJunJulAugSepOctNovDecResult
not in top 100not in top 100
not in top 100not in top 100
not in top 100not in top 100not in top 100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]81[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]N2[/TH]
[TD="align: left"]=IF(ISNUMBER(INDEX(A2:L2,,MATCH(TRUE,INDEX(A2:L2<>"",0),0))),INDEX(A2:L2,,MATCH(TRUE,INDEX(A2:L2<>"",0),0)),100)
-IF(ISNUMBER(LOOKUP(2,1/(A2:L2<>""),A2:L2)),LOOKUP(2,1/(A2:L2<>""),A2:L2),100)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Matching a number sequence within a group of numbers in a cell
Replies
1
Views
542
FormR
FormR
M
  • Question Question
Index Match function List based on date
Replies
7
Views
271
Fluff
Fluff
staticfluids
  • Question Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
386
staticfluids
staticfluids
S
  • Solved
Dynamic Average of top values in specific ranges
Replies
3
Views
569
Fluff
Fluff
L
  • Question Question
Index match working but not all information being brought in
Replies
3
Views
514
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top