Right most value minus left most value

[mco5044]

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!

 

[mco5044]

Hey,

Thank you guys so much you have no idea how helpful this has been for such a complicated formula. The winning equation is below!

=IF(ISNUMBER(INDEX(K4:V4,,MATCH(TRUE,INDEX(K4:V4<>"",0),0))),INDEX(K4:V4,,MATCH(TRUE,INDEX(K4:W4<>"",0),0)),100)
-IF(ISNUMBER(LOOKUP(1E+100,K4:V4)),LOOKUP(1E+100,K4:V4),100)

 

[T. Valko]

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)

That's exactly what this formula is doing:

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

This returns the value of the 1st non-empty cell from left to right:

INDEX(A2:E2,MATCH(TRUE,A2:E2<>"",0))

If the entry is a TEXT value then the double unary -- will cause it to generate an error and if there's an error then IFERROR will return 100.

If the entry is a number then the double unary -- has no net effect and IFERROR will return that number.

 

[Marcol]

Hmm?

=IF(ISERROR(--INDEX(A2:L2,MATCH(TRUE,A2:L2<>"",0))),100,--INDEX(A2:L2,MATCH(TRUE,A2:L2<>"",0)))
-IF(ISNUMBER(LOOKUP(2,1/(A2:L2<>""),A2:L2)),LOOKUP(2,1/(A2:L2<>""),A2:L2),100)

Would be shorter for pre-2007.

Use IFERROR(),as T. Valko suggests, for the first part if only to work with later versions.

I used LOOKUP(2,1/(A2:L2<>""),A2:L2) rather than LOOKUP(1E100,A2:E2) in case the rightmost cell contained "not in top 100".

I don't have 2007 at the moment so can't try to better the second part of the formula.
Maybe ...

-IFERROR(LOOKUP(2,1/(A2:L2<>""),A2:L2))*1,100)

 

[T. Valko]

I would still be interested in seeing a sample file to see what the data actually looks like so I can see what I can come up with.

 

[mco5044]

I would still be interested in seeing a sample file to see what the data actually looks like so I can see what I can come up with.

You can download an example sheet here http://www.mediafire.com/view/?swt9virxva46n13

We were using this to track progress on SEO clients so you can see why we needed the formula to be written the way it was. Thanks again you guys!

 

[T. Valko]

You can download an example sheet here http://www.mediafire.com/view/?swt9virxva46n13

We were using this to track progress on SEO clients so you can see why we needed the formula to be written the way it was. Thanks again you guys!

I keep getting a page that says: Temporarily Unavailable. Please check back soon.

Oh well!

At least you have something that works!

 

[mco5044]

I keep getting a page that says: Temporarily Unavailable. Please check back soon.

Oh well!

At least you have something that works!

When I click on the link through Chrome it takes me right to the spreadsheet and allows me to view it or download it.

Maybe you need to log in to media fire? You can just make a quick free profile

 

[Marcol]

See if you can get this from my Skydrive, I'm no good at sharing files with this MS creation it works with luck rather than good judgement.
mco5044_Book1

I've added the non-array formula in Column N and the array in Column P

Both will work, however your file is peppered with cells containing single spaces, not blanks or null strings.
e.g. Row 21
These cells throw both formulae and rather than trying to fiddle the formulae, you should clear these cells.

Personally I'd go with a non-array formula.


What is the expected result when there is only one value in a row?

 

[mco5044]

See if you can get this from my Skydrive, I'm no good at sharing files with this MS creation it works with luck rather than good judgement.
mco5044_Book1

I've added the non-array formula in Column N and the array in Column P

Both will work, however your file is peppered with cells containing single spaces, not blanks or null strings.
e.g. Row 21
These cells throw both formulae and rather than trying to fiddle the formulae, you should clear these cells.

Personally I'd go with a non-array formula.


What is the expected result when there is only one value in a row?

I see that both formulas work, but when it gets into the really technical excel stuff like array formulas and committing them with ctrl shift enter I don't have that deep of an understanding to be able to tell why the non array formula is better.

Thanks for the tip with single space cells, I will keep that in mind for the future when I am creating more dashboards.

The end result is the variance, so if there is only 1 number that means the variance (difference from starting to ending) would be 0.

Thanks Again for all of your help

 

[Marcol]

Okay, I think we are there now. Clean the cells with spaces and use

=IF(COUNTA(B5:M5)=0,"",
IF(ISERROR(INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1),100,INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1)
-IF(ISERROR(LOOKUP(2,1/(B5:M5<>""),B5:M5)*1),100,LOOKUP(2,1/(B5:M5<>""),B5:M5)*1))

This for 2007 and above could be ... (Check it, I don't have 2007 or above at present)

=IF(COUNTA(B5:M5)=0,"",
IFERROR(INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1,100)
-IFERROR(LOOKUP(2,1/(B5:M5<>""),B5:M5)*1,100))

Go for non-array formula when ever possible they are usually less memory intense.
See this link http://www.ozgrid.com/Excel/arrays.htm

As a matter of interest, did you manage to down-load the file from My Skydrive?
If so, was it easily done?

Excel 2003

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2003 Array
Company A
Company B
Company C
Company D
Company E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Ranking on the day of the report[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Variance[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[TD="align: center"]May[/TD]
[TD="align: center"]Jun[/TD]
[TD="align: center"]Jul[/TD]
[TD="align: center"]Aug[/TD]
[TD="align: center"]Sep[/TD]
[TD="align: center"]Oct[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: center"]Dec[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

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

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

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

[TD="align: center"]6[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

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

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

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

[TD="align: center"]8[/TD]

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

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

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

[TD="align: center"]9[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]97[/TD]
[TD="align: right"][/TD]
[TD="align: right"]97[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]95[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"]98[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"]91[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]not in top 100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]#NAME?[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/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"]N5[/TH]
[TD="align: left"]=IF(COUNTA(B5:M5)=0,"",IFERROR(INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1,100)-IFERROR(LOOKUP(2,1/(B5:M5<>""),B5:M5)*1,100))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]P5[/TH]
[TD="align: left"]=IF(COUNTA(B5:M5)=0,"",IF(ISERROR(INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1),100,INDEX(B5:M5,,MATCH(TRUE,INDEX(B5:M5<>"",0),0))*1)-IF(ISERROR(LOOKUP(2,1/(B5:M5<>""),B5:M5)*1),100,LOOKUP(2,1/(B5:M5<>""),B5:M5)*1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]R5[/TH]
[TD="align: left"]=IF(COUNTA(B5:M5)=0,"",IF(ISERROR(--INDEX(B5:M5,MATCH(TRUE,B5:M5<>"",0))),100,--INDEX(B5:M5,MATCH(TRUE,B5:M5<>"",0)))-IF(ISNUMBER(LOOKUP(2,1/(B5:M5<>""),B5:M5)),LOOKUP(2,1/(B5:M5<>""),B5:M5),100))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]