IFerror index problems, Im stuck

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
Hi I use this formula to calculate losses in weight and it works fine:
=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"")
When I use the C10:c62 it works fine.

The problem is that i also want to use 6 cells not in a row, like C10, C14, C16, C20, C22, C24
I just cant get the formula to work.
=IFERROR(INDEX(C10;C14;C16;C20;C22;C24;MATCH(9^9;C10;C14;C16;C20;C22;C24))-C6;"")
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
There is probably an easier way. How is your data laid out? Where do your results need to appear?
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
Hi AliGW, thanks for feedback.

In Cell C1 I have the result.

In Cell C4 I got my 1 measurment lets says 2 CM its my reference.
In Cell C8 it got my 2 measurment 3 CM Then i Want the Cell C1 to display 1 CM (C4+C8)
In Cell C13 it got my 3 measurment 4 CM Then i Want the Cell C1 to display 2 CM (C4-C13)
In Cell C 16 I maybe then have 3 CM and I then want C1 ti display 1 cm.

I need it always to take the newest measurment and substract from C4 and display in C1

Then when I do a new measurment and put it into Cell C12 = 2 cm, I want Cell C1 to show the diffence between the newest input and the reference.
 

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
Please provide a visual - preferably an editable table. Grab the ForumTools add-in (link in my signature line) to do this.
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12

ADVERTISEMENT

Sorry to say byt cant find the tool.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
Hi I use this formula to calculate losses in weight and it works fine:
=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"")
When I use the C10:c62 it works fine.

Are you fishing for the last numeric value? If so...

=LOOKUP(9.99999999999999E+307;C10:C62)

The problem is that i also want to use 6 cells not in a row, like C10, C14, C16, C20, C22, C24
I just cant get the formula to work.
=IFERROR(INDEX(C10;C14;C16;C20;C22;C24;MATCH(9^9;C10;C14;C16;C20;C22;C24))-C6;"")

Possibly...

=IFERROR(LOOKUP(9,99999999999999E+307;CHOOSE(ROW(INDIRECT("1:"&COUNT(C10;C14;C16;C20;C22;C24)));C10;C14;C16;C20;C22;C24))-C6,"")

This assumes a comma for decimals and a semi-colon as list separator.
 

DonBDK

New Member
Joined
Dec 29, 2016
Messages
12
=IFERROR(INDEX(C10:C62;MATCH(9^9;C10:C62))-C6;"") this works perfect.

The problems is when the cells C10:C16 is located like C10 C12 C16 C20 C22 C24 and not just under each other

 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,366
Members
430,424
Latest member
ALHK022

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
Top