Understand the logic behind a table contains MAX in a range and a calculation

[petrovit]

Hi Guys,

Could you please assist me with the below logic?

I have the below table:

A	B	C	D	E	F
2022-08-02 09:06:03	1111@yahoo.de	1000​	EUR	credit_card	Declined
2022-08-02 09:31:06	1111@yahoo.de	3000​	EUR	credit_card	Declined
2022-08-02 09:36:03	1111@yahoo.de	1000​	EUR	credit_card	Completed
2022-08-02 09:39:50	1111@yahoo.de	1000​	EUR	credit_card	Declined

I would like to have the logic of:

if a user (email) received a decline what is the amount that I have lost?

In this case, we have user 1111@yahoo.de that completed 1000EUR transaction but tried the highest amount of 3000EUR, so we have lost 2000EU.

I want to take the highest amount that we have tried and to know how much we succeeded minos declined which will give me how much EUR I have lost.





I am taking the MAX.

For example, in here I wanted to charge the client only total 3K, but in reality couldn't (only 1K confirmed) so this is the reason for lost of 2K.
All the trails are not should take into consideration, only the MAX amount.


Why is 2000 the correct answer when two different 1000 were declined? Because the bid was on the MAX amount all the rest are trials to try to reach the MAX.

How can I do so?
I have several clients which I would like to have a formula on all of them.

Many thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you are trying to do?

22 10 30.xlsm
	A	B	C	D	E	F	G	H	I
1	2/08/2022 9:06	1111@yahoo.de	1000	EUR	credit_card	Declined		1111@yahoo.de	2000
2	2/08/2022 9:31	1111@yahoo.de	3000	EUR	credit_card	Declined
3	2/08/2022 9:36	1111@yahoo.de	1000	EUR	credit_card	Completed
4	2/08/2022 9:39	1111@yahoo.de	1000	EUR	credit_card	Declined
Lost

Cell Formulas
Range	Formula
I1	I1	=MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Declined")-MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Completed")

 

[petrovit]

Looks great! Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[petrovit]

I can see that if there is an only declines:

A	B	C	D	E	F
2022-08-02 09:06:03	1111@yahoo.de	1000	EUR	credit_card	Declined
2022-08-02 09:31:06	1111@yahoo.de	3000	EUR	credit_card	Declined
2022-08-02 09:36:03	1111@yahoo.de	1000	EUR	credit_card	Declined
2022-08-02 09:39:50	1111@yahoo.de	1000	EUR	credit_card	Declined

The same function

=MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Declined")-MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Completed")

Will extract the results 3000-1000=2000, which is an incorrect as no transactions went through and the results should be 3000. How to do so?

 

[petrovit]

I can see that if there is an only declines:

A	B	C	D	E	F
2022-08-02 09:06:03	1111@yahoo.de	1000	EUR	credit_card	Declined
2022-08-02 09:31:06	1111@yahoo.de	3000	EUR	credit_card	Declined
2022-08-02 09:36:03	1111@yahoo.de	1000	EUR	credit_card	Declined
2022-08-02 09:39:50	1111@yahoo.de	1000	EUR	credit_card	Declined

The same function

=MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Declined")-MAXIFS(C$1:C$4,B$1:B$4,H1,F$1:F$4,"Completed")

Will extract the results 3000-1000=2000, which is an incorrect as no transactions went through and the results should be 3000. How to do so?

My mistake. it will give you 3000. Thanks

 

[Peter_SSs]

My mistake. it will give you 3000. Thanks

Cheers.