IFerror index problems, Im stuck

[DonBDK]

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;"")

 

[Aladin Akyurek]

=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

Did you see post #9? If you did, what is the answer?

 

[DonBDK]

Given C6:C24 below, what is the expected result?

Result C6 is the reference = 82
80 = -2
82 = 0
83 = 1
81 = -1
83 = 1
84 = 2
83 = 1

 

[Aladin Akyurek]

Given C6:C24 below, what is the expected result?

Result C6 is the reference = 82
80 = -2
82 = 0
83 = 1
81 = -1
83 = 1
84 = 2
83 = 1

I have 80 in C6, not 82. Try to specify the output for the input I posted...

Row\Col	C​
6​	80​
7​
8​
9​
10​	82​
11​
12​
13​
14​	83​
15​
16​	81​
17​
18​
19​
20​	83​
21​
22​	84​
23​
24​	83​

Try also to imagine that the cells shown as empty can also have numbers (unless they don't in your spreadsheet).

 

[DonBDK]

Maybe I should find another fucntion.

What i really want is to have exce, to find the newest typed data and substract it from the reference and display it in the display cell

I have 80 in C6, not 82. Try to specify the output for the input I posted...

Row\Col	C​
6​	80​
7​
8​
9​
10​	82​
11​
12​
13​
14​	83​
15​
16​	81​
17​
18​
19​
20​	83​
21​
22​	84​
23​
24​	83​

<tbody>
</tbody>

Try also to imagine that the cells shown as empty can also have numbers (unless they don't in your spreadsheet).

 

[Aladin Akyurek]

Maybe I should find another fucntion.

What i really want is to have exce, to find the newest typed data and substract it from the reference and display it in the display cell

Maybe you should just post the output you want for the input I provided to your specifications...

 

[DonBDK]

The outout should be the most recent data typed in a cell.
Output = newest tyoed data - Reference cell.

Thats it.

Every week i will type in a number in a new cell and i just want it to automatic update that.

 

[Aladin Akyurek]

The outout should be the most recent data typed in a cell.
Output = newest tyoed data - Reference cell.

Thats it.

Every week i will type in a number in a new cell and i just want it to automatic update that.

Geez...

1. Just enter:

=LOOKUP(9.99999999999999E+307,C:C)

will return the last numeric value from column C.


2. Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE(ROW(INDIRECT("1:"&COUNT(C10,C14,C16,C20,C22,C24))),C10,C14,C16,C20,C22,C24))

will return the last numeric value from the set of selected cells. In this case: C10,C14,C16,C20,C22,C24.

Good luck.

 

[DonBDK]

Thanks but i cant get it to accept the formed 2.

Could there be a wrong spelling..
=LOOKUP(9.99999999999999E+307,CHOOSE(ROW(INDIRECT("1:"&COUNT(C10,C14,C16,C20,C22,C24))),C10,C14,C16,C20,C22,C24))

Geez...

1. Just enter:

=LOOKUP(9.99999999999999E+307,C:C)

will return the last numeric value from column C.


2. Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE(ROW(INDIRECT("1:"&COUNT(C10,C14,C16,C20,C22,C24))),C10,C14,C16,C20,C22,C24))

will return the last numeric value from the set of selected cells. In this case: C10,C14,C16,C20,C22,C24.

Good luck.

 

[Aladin Akyurek]

Thanks but i cant get it to accept the formed 2.

Could there be a wrong spelling..
=LOOKUP(9.99999999999999E+307,CHOOSE(ROW(INDIRECT("1:"&COUNT(C10,C14,C16,C20,C22,C24))),C10,C14,C16,C20,C22,C24))

3.4 >> 3,4
9.99... >> 9,99...

in non-American or European systems.

And a list separator comma in an American system like in...

SUM(A2:B2,X2)

becomes a semi-colon in a non-American or in an European system, i.e.

SUM(A2:B2;X2)

 

[DonBDK]

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

Its still not working. I can get the data from the first cell, but nothing happens when i put data into the other cell-.

3.4 >> 3,4
9.99... >> 9,99...

in non-American or European systems.

And a list separator comma in an American system like in...

SUM(A2:B2,X2)

becomes a semi-colon in a non-American or in an European system, i.e.

SUM(A2:B2;X2)