# IF statements for table array

#### Tizza

##### New Member
Hi,

I want to use a simple if statement to return a value in a table array.

For Example
Rate = 0.2
A1: 100
A2: 150
A3: 200
A4: Future data entry
A5: Future data entry

Where I require the calculation to take the most recent value "A3" and subtract from the its predesessor "A2", then multiply by the rate (0.2).

Where I am getting stuck is when future data is entered i.e. "A4" (Making "A5" future data entry) and therefore must subtract from "A3", and multiply by rate (0.2)

Hope this makes sense

Thanks

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Ganjin

##### Board Regular
doubt this is the best way to do this, but:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>100</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>0.2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>150</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>200</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>250</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>
Cell B1 is Rate
Cell B2 Formula:
=B1*(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000<>""),1),0)-INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000<>""),2),0))
Ctrl+Shift+Enter

#### Haseeb Avarakkan

##### Well-known Member
Hello,

If you don't have blank cells before the last value, try this

=(LOOKUP(1E+307,A:A)-INDEX(A:A,MATCH(1E+307,A:A)-1))*0.2

#### AlphaFrog

##### MrExcel MVP
Excel Workbook
ABC
1EntriesRateResult
21000.210
3150**
4200**
Sheet

=B2 * (INDEX(A:A, MATCH(9E+307,A:A,1)) - INDEX(A:A, MATCH(9E+307,A:A,1)-1))

The Blue returns the last number in column A
The Red returns the 2nd to last number in column A
There has to be a least two numbers in column A

Last edited:

#### Tizza

##### New Member
Thanks all,

I ended up using a Lookup which worked perfectly

Cheers,

Tizza

Replies
8
Views
119
Replies
1
Views
97
Replies
0
Views
92
Replies
6
Views
100
Replies
3
Views
133

Threads
1,171,699
Messages
5,877,001
Members
433,228
Latest member
Sarah1989

### 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

### 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