We help IT Professionals succeed at work.

excel formula get most right/left values from a row

Jarkko Pelli
Jarkko Pelli asked
on
Hi!

Could someone help me with an Excel formula?

kuva.PNG
Im trying to write a formula, that would read columns F to J , per row, and check what the most left value is and the most right value. Then it would subtract these from each others, as shown in the result field.
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Are they always going to be in ascending order?

If so, you can use MIN() and MAX() and subtract one from the other.

Author

Commented:
Thanks Philip, unfortunately the numbers are not always in acending order, .
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
These are array formulas. So you have to enter them, then press Ctrl+Alt+Enter instead of just Return.
 
Here are the formulas:
 
=INDEX($F3:$J3,SMALL(IF($F3:$J3<>"",COLUMN($F3:$J3)-COLUMN($F3)+1),1))
=INDEX($F3:$J3,SMALL(IF($F3:$J3<>"",COLUMN($F3:$J3)-COLUMN($F3)+1),COUNT($F3:$J3)))

Author

Commented:
Thanks for the help! I keep getting an #NAME? error when using the formula. Im using Excel 2010.. Would it be possible to post an example of the Excel sheet ?
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
These are array formulas. So you have to enter them, then press Ctrl+Alt+Enter instead of just Return, otherwise you will get #NAME? error.

Please find attached.
EE150227.xlsx

Author

Commented:
Its Ctrl + shift + Enter in Office 2010 ;) Thanks, its working now! :)