Return an amount with a criteria

[Marwan69]

Hello everyone and please pay a smile when you work this because it's a pain...

I have a table and I'd like to return an amount based on criteria. Let me explain below:
[TABLE="width: 417"]
<tbody>[TR]
[TD]Financial Figures (SR'000)[/TD]
[TD]Salama[/TD]
[TD]Yearly Change(%)[/TD]
[/TR]
[TR]
[TD]Cash & Cash Equivalents[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance Operations(Takaful Operations)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash on hand and at banks[/TD]
[TD]36,465[/TD]
[TD]-35.35%[/TD]
[/TR]
[TR]
[TD]Term deposits - (<= 3 mos)[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Term deposits - (>3<12 mos)[/TD]
[TD]10,576[/TD]
[TD]190.65%[/TD]
[/TR]
[TR]
[TD]Shareholders' Operations[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash on hand and at banks[/TD]
[TD]33[/TD]
[TD]16639.39%[/TD]
[/TR]
[TR]
[TD]Term deposits - (<= 3 mos)[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Term deposits - (>3<12 mos)[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Insurance Operations(Takaful Operations)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash on hand and at banks[/TD]
[TD]23,573[/TD]
[TD]-88.58%[/TD]
[/TR]
[TR]
[TD]Term deposits - (<= 3 mos)[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Term deposits - (>3<12 mos)[/TD]
[TD]30,739[/TD]
[TD]-64.34%[/TD]
[/TR]
[TR]
[TD]Shareholders' Operations:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cash on hand and at banks[/TD]
[TD]5,524[/TD]
[TD]-99.86%[/TD]
[/TR]
[TR]
[TD]Term deposits - (<= 3 mos)[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like the formula to return -35.35% under (Yearly change%) column but based on the following criteria:
I want the formula to read this criteriaInsurance Operations(Takaful Operations)) and this criteria: (Cash on hand and at banks) and therefore return to me the (Yearly change%). Hope is clear.

Why is that? Because I have two operations: (Insurance operations(Takaful Operations) and (Shareholders' Operations). I want first to return the yearly change% pertaining to insurance operations and then jump to shareholders' operations.

I wish this didn't cause pain to you guys!!!Many thanks,
Marwan

 

[Michael M]

can you provide us with the numbers that returned 35.35%, because your criteria appears on a number of lines ???

 

[Marwan69]

Yes of course and thanks for replying bro.

- 35.35% is a product of dividing Cash on hand and at banks of 23,573/36,465-1

Many thanks,
Marwan

 

[Michael M]

You seem to have answered your own question....why don't you simply do what you have described ??

Excel 2007
	A	B	C
4	Insurance Operations(Takaful Operations)
5	Cash on hand and at banks	36,465	-35.35%
6	Term deposits - (<= 3 mos)	0
7	Term deposits - (>3<12 mos)	10,576	190.65%
8	Shareholders' Operations
9	Cash on hand and at banks	33	16639.39%
10	Term deposits - (<= 3 mos)	0
11	Term deposits - (>3<12 mos)	0
12	2014
13	Insurance Operations(Takaful Operations)
14	Cash on hand and at banks	23,573	-88.58%
15	Term deposits - (<= 3 mos)	0
16	Term deposits - (>3<12 mos)	30,739	-64.34%
17	Shareholders' Operations:
18	Cash on hand and at banks	5,524	-99.86%
19	Term deposits - (<= 3 mos)	0
Sheet1

Cell Formulas
Range	Formula
C5	=B14/B5-1

 

[Marwan69]

Hello Michael M.

I didn't get that and I still can't see how this can be done. I'll explain again in a simple way hopefully and I know this is quite confusing.
I have two portfolios, right? one is called:Insurance Operations(Takaful Operations), and the other one called: Shareholders' Operations. I want the formula when it runs reads two criterias: The insurance Operations(Takaful Operations) and Cash on hand and at banks, and returns to me the amount(Percentage) which is -35.35%. So that when I drag the formula down the table it only reads the criterea that I specified, and it will not pick the shareholders' Operations.

Hope is clear.

Many thanks,
Marwan

 

[Michael M]

you can't drag a formula down the column and skip certain rows....unless you apply it with VBA !

 

[Marwan69]

Thanks Michael M. I mean when I apply the formula it should read only the defined criterea.

Many thanks,
Marwan

 

[RasGhul]

Hi Marwan,

So when you get your report, each years data is the same number of rows and layout?

 

[Marwan69]

Hi RasGhul.

Let me illustrate:

let's say there's a formula. Say the formula reads as follows (I know it's not true but this was my trial):

=IFERROR(INDEX(I6:I49,MATCH("Cash on hand and at banks",$G$6:$G$49,0)),"")

Where: I6:I49 (Reads the Yearly Change%)
$G$6:$G$49(Reads A4:A...)
and "Cash on hand and at banks": Picks only this text and return the value in the (Yearly Change%).

But again I want also another criterea besides "Cash on hand and at banks" which is: Insurance Operations(Takaful Operations). Why? Because we've got the (Shareholders' Operations) which is not of my interest to pickup its value at this stage.

Guys if this is going to cause you any pain please ignore it and thanks a lot for showing the eager to help, which is the most important thing between us here in just working out a simple formula.

Many thanks,
Marwan

 

[RasGhul]

Not sure if this is what you need and assumes that the Cash on hand row ALWAYS follows the "Takaful" or "Shareholders" rows;

Drag down the D1 formula as helper for index & match;

Not the most efficient solution sorry.

Book1
	A	B	C	D	E	F
1	2013	Salama	Yearly Change(%)	2013
2	Insurance Operations(Takaful Operations)			2013	YEAR	2013
3	Cash on hand and at banks	36,465	-35.35%	2013	CRITERIA	Insurance Operations(Takaful Operations)
4	Term deposits - (<= 3 mos)	0		2013	RESULT	-35.35%
5	Term deposits - (>3<12 mos)	10,576	190.65%	2013
6	Shareholders' Operations			2013
7	Cash on hand and at banks	33	16639.39%	2013
8	Term deposits - (<= 3 mos)	0		2013
9	Term deposits - (>3<12 mos)	0		2013
10	2014			2014
11	Insurance Operations(Takaful Operations)			2014
12	Cash on hand and at banks	23,573	-88.58%	2014
13	Term deposits - (<= 3 mos)	0		2014
14	Term deposits - (>3<12 mos)	30,739	-64.34%	2014
15	Shareholders' Operations			2014
16	Cash on hand and at banks	5,524	-99.86%	2014
17	Term deposits - (<= 3 mos)	0		2014
Sheet1

Cell Formulas
Range	Formula
D1	=IF(ISNUMBER(A1),A1,OFFSET(A1,-1,3))
F4	=OFFSET(INDEX($C$1:$C$17,MATCH(F3&F2,INDEX($A$1:$A$17&$D$1:$D$17,,),0)),1,,)