Formula errors?

[Aberdham]

Hi,

Could anyone take a look into the formula for me please?

It seems something went wrong, and all the result Returns to be "0"

SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RD"&"";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RC"&"";'Lease-Sales'!$B$462:$B$579));('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579);(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3))))


Your assistance will be greatly appreciated !!

 

[Marcelo Branco]

Not sure what you are trying to do but you need to convert (coerce) the logical values in the comparisons to 0 or 1.
I recommend the double unary-operator --, that is:
--('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579)
and
--(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3)))

Hope this helps

M.

 

[Aberdham]

Not sure what you are trying to do but you need to convert (coerce) the logical values in the comparisons to 0 or 1.
I recommend the double unary-operator --, that is:
--('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579)
and
--(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3)))

Hope this helps

M.

Thanks for your reply! So I have These 3 tables:

Table 1	Revenue
SN	Invoice no.	Customer	description	amount Dollar	exchange rate	amount euro
158958	15RD000268	ASD	process_fee_23.04-22.05.2017_SN_158958	$ 38.850,00	$ 1,2361	€ 31.429,50
158958	15MD000158	ASD	registration_fee_01.03-31.03.2017_ESN_158958	$ 57.722,00	$ 1,2323	€ 46.840,87
158958	15RC000158	ASD	process_fee_23.04-22.05.2017_ESN_158958	$ 2.102,00	$ 1,2323	€ 1.705,75
158958	15MC000159	ASD	registration_fee_01.03-31.03.2017_ESN_158958	$ 5.541,00	$ 1,2323	€ 4.496,47

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

Table 2	Cost
SN	Invoice no.	Customer	description	amount Dollar	exchange rate	amount euro
158958	SAEL-18-0036-04	ASD	process_fee_10.04-09.05.2017_SN_158958	$ 37.233,49	$ 1,2308	€ 30.251,45
158958	200-PANIN-000058	ASD	registration_fee_01.03-31.03.2017_SN_158958	$ 82.483,14	$ 1,2321	€ 66.945,17

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

Table 3
SN	PF (Revenue)	RF (Revenue)	PF (cost)	RF (cost)
158958	€ 29.723,74	€ 42.344,40	€ 30.251,45	€ 66.945,17

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Table 1 and Table 2 are the Output tables and table 3 is the Input table

PF and RF in table 3 are taken from table 1
and the PF and RF from the cost side is extracted from table 2

and the formula i am using now is:
=SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND("RD";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND("RC";'Lease-Sales'!$B$462:$B$579));--('Lease GP April'!$B9='Lease-Sales'!$A$462:$A$579);--(C$2=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$2))))

and return to "0"

can you please tell me what went wrong? Thank you.

 

[Marcelo Branco]

I think you meant Table 1 and Table 2 are the Input tables (given) and table 3 is the Output table (desired results)

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​
1​	Table 1​	Revenue​
2​	SN​	Invoice no.​	Customer​	description​	amount Dollar​	exchange rate​	amount euro​
3​	158958​	15RD000268​	ASD​	process_fee_23.04-22.05.2017_SN_158958​	38.850,00​	1,2361​	31.429,50​
4​	158958​	15MD000158​	ASD​	registration_fee_01.03-31.03.2017_ESN_158958​	57.722,00​	1,2323​	46.840,87​
5​	158958​	15RC000158​	ASD​	process_fee_23.04-22.05.2017_ESN_158958​	2.102,00​	1,2323​	1.705,75​
6​	158958​	15MC000159​	ASD​	registration_fee_01.03-31.03.2017_ESN_158958​	5.541,00​	1,2323​	4.496,47​
7​
8​
9​	Table 2​	Cost​
10​	SN​	Invoice no.​	Customer​	description​	amount Dollar​	exchange rate​	amount euro​
11​	158958​	SAEL-18-0036-04​	ASD​	process_fee_10.04-09.05.2017_SN_158958​	37.233,49​	1,2308​	30.251,45​
12​	158958​	200-PANIN-000058​	ASD​	registration_fee_01.03-31.03.2017_SN_158958​	82.483,14​	1,2321​	66.945,17​
13​
14​	Table 3​
15​	SN​	PF (Revenue)​	RF (revenue)​	PF (cost)​	RF (cost)​
16​	158958​	29.723,74​	42.344,40​	30.251,45​	66.945,17​

<tbody>
</tbody>

Values in blue
Formula in B16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("RD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("RC",$B$3:$B$6))*$G$3:$G$6)

Formula in C16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("MD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("MC",$B$3:$B$6))*$G$3:$G$6)

It's not clear what are the criteria to get the values in red (D16 and E16). Could you clarify?

M.

 

[Aberdham]

I think you meant Table 1 and Table 2 are the Input tables (given) and table 3 is the Output table (desired results)

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​
1​	Table 1​	Revenue​
2​	SN​	Invoice no.​	Customer​	description​	amount Dollar​	exchange rate​	amount euro​
3​	158958​	15RD000268​	ASD​	process_fee_23.04-22.05.2017_SN_158958​	38.850,00​	1,2361​	31.429,50​
4​	158958​	15MD000158​	ASD​	registration_fee_01.03-31.03.2017_ESN_158958​	57.722,00​	1,2323​	46.840,87​
5​	158958​	15RC000158​	ASD​	process_fee_23.04-22.05.2017_ESN_158958​	2.102,00​	1,2323​	1.705,75​
6​	158958​	15MC000159​	ASD​	registration_fee_01.03-31.03.2017_ESN_158958​	5.541,00​	1,2323​	4.496,47​
7​
8​
9​	Table 2​	Cost​
10​	SN​	Invoice no.​	Customer​	description​	amount Dollar​	exchange rate​	amount euro​
11​	158958​	SAEL-18-0036-04​	ASD​	process_fee_10.04-09.05.2017_SN_158958​	37.233,49​	1,2308​	30.251,45​
12​	158958​	200-PANIN-000058​	ASD​	registration_fee_01.03-31.03.2017_SN_158958​	82.483,14​	1,2321​	66.945,17​
13​
14​	Table 3​
15​	SN​	PF (Revenue)​	RF (revenue)​	PF (cost)​	RF (cost)​
16​	158958​	29.723,74​	42.344,40​	30.251,45​	66.945,17​

<tbody>
</tbody>

Values in blue
Formula in B16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("RD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("RC",$B$3:$B$6))*$G$3:$G$6)

Formula in C16
=SUMPRODUCT(--($A$3:$A$6=A16),ISNUMBER(SEARCH("MD",$B$3:$B$6))*$G$3:$G$6-ISNUMBER(SEARCH("MC",$B$3:$B$6))*$G$3:$G$6)

It's not clear what are the criteria to get the values in red (D16 and E16). Could you clarify?

M.

The red ones are from table 2
G11 and G12 respectively,
however, there's no define rules for invoice number such as the ones in table 1

 

[Marcelo Branco]

The red ones are from table 2
G11 and G12 respectively,
however, there's no define rules for invoice number such as the ones in table 1

If there are no rules to follow how can one create a formula to get the proper value?

M.

 

[Aberdham]

If there are no rules to follow how can one create a formula to get the proper value?

M.

To link the SN and the description?

since in table 3 the PF and RF stands for process fee and registration fee respectively.
Perhaps to use a recognition and sum product or aggregate would do the job?

would be great if you could help.
Thanks!

 

[Marcelo Branco]

To link the SN and the description?

since in table 3 the PF and RF stands for process fee and registration fee respectively.

Try
D16
=SUMPRODUCT(--($A$11:$A$12=A16),--ISNUMBER(SEARCH("process",$D$11:$D$12)),$G$11:$G$12)

E16
=SUMPRODUCT(--($A$11:$A$12=A16),--ISNUMBER(SEARCH("registration",$D$11:$D$12)),$G$11:$G$12)

M.

 

[Aberdham]

If there are no rules to follow how can one create a formula to get the proper value?

M.

and at the end of the description, there is always an indication of _SN xxxxxx
so perhaps to satisfy the description as PF(process fee) or RF (registration fee) as well as the SN
we have the formula?

 

[Marcelo Branco]

and at the end of the description, there is always an indication of _SN xxxxxx
so perhaps to satisfy the description as PF(process fee) or RF (registration fee) as well as the SN
we have the formula?

See post 8

M.