Profit based on sliding price scale

[honkin]

I need to make a calculation for profit, but am struggling due to the fact the actual price is a calculation based on another price. If the original price is is any of 3 price ranges, it has a multiplication factor

Here is the normal profit calculation

=IF(AO21<>"",IF(K21<>J21,(AO21-1)*1,-1),"0")

Simply if K (Home Goals) does not equal J (Away Goals), then we do a simple profit calculation based on the price in AO. We are betting the game is NOT a draw

Here's the rub, though; the original price is to Back the draw and we don't have the Lay prices in this sheet, so we are approximating. There are 3 price ranges and this formula works correctly adjusting the price from AO.

=IF(AO21<=3,L20*1.1,IF(AO21<=10,L20*1.15,IF(AO22>10,L20*1.2,"0")))

So basically if price is <=3, price is increased by 10%; if <=10, price is increased by 15% and if over 10, it is increased by 20%.

How is it possible to have the codes combined so that profit or loss is calculated on any of the 3 possible prices and not the original price in AO?

Thanks in advance

 

[bebo021999]

Fast answer needs visual help.
Pls attach a screenshot/XL2BB mini sheet.

 

[honkin]

Fast answer needs visual help.
Pls attach a screenshot/XL2BB mini sheet.

Thanks for your reply. Happy to supply it, but not sure any visualisation will help much, as it is just a static price.

Predictology AI Export Apr with Reporting.xlsb
	AM	AN	AO	AP
20	True Odds	Odds Home	Odds Draw	Odds Away
21	2.25	3.36	3.47	2.16
22	6.2	1.56	4.28	5.56
23	3.2	2.17	3.55	3.23
24	2.9	2.22	3.58	3.13
25	7.29	1.32	5.3	9.02
26	3.6	1.84	3.85	4.05
27	1.36	14.83	7.7	1.17
28	2.57	3.19	3.52	2.04
29	3.33	2.83	3.14	2.4
30	3.16	1.94	3.28	3.92
31	3.54	1.9	3.1	4.22
32	4.63	1.8	3.63	4.17
33	3.49	1.98	3.28	3.73
34	6.48	1.17	6.41	13.21
35	2.66	2.31	3.37	3.15
36	3.12	2.07	3.21	3.51
rc-vdw-place-adapted-2021-01-01

The price in AO is used as the base, but the second calculation I supplied adjusts that price based where it sits in the price range.

cheers

 

[bebo021999]

Its not enough information.
mini should display all cells inlvoved, like K21,J21,L20, with their formula (if any)
All other columns that not relevant, should be hidden, before copy mini sheet.

 

[honkin]

Its not enough information.
mini should display all cells inlvoved, like K21,J21,L20, with their formula (if any)
All other columns that not relevant, should be hidden, before copy mini sheet.

Cheers

I just noticed the original price formula was incorrect (copied from something else I was working on). Here it is correctly.

=IF(AO21<=3,AO21*1.1,IF(AO21<=10,AO21*1.15,IF(AO21>10,AO21*1.2,"0")))

Here is the sheet with only J, K & AO

Predictology AI Export Apr with Reporting.xlsb
	J	K																														AO
20	FT H	FT A																														Odds Draw
21	1	2																														3.47
22	4	0																														4.28
23	0	0																														3.55
24	0	3																														3.58
25	1	1																														5.3
26	2	2																														3.85
27	0	2																														7.7
28	1	3																														3.52
29	3	1																														3.14
30	1	2																														3.28
31	1	1																														3.1
32	1	0																														3.63
33	1	1																														3.28
34	4	0																														6.41
35	4	0																														3.37
36	1	1																														3.21
37	1	0																														3.46
38	1	0																														3.05
39	4	0																														3.21
40	2	4																														3.3
41	0	0																														3.37
42	0	1																														3.6
43	1	3																														3.71
44	1	1																														3.79
45	1	1																														3.11
46	0	2																														3.8
47	2	1																														4.77
48	1	2																														3.29
49	5	2																														8.9
50	1	1																														3.16
51	2	0																														3.43
52	1	1																														3.96
53	0	0																														3.34
54	3	2																														3.67
55	0	0																														3.27
56	0	3																														3.52
rc-vdw-place-adapted-2021-01-01

Thanks so much for taking the time

 

[honkin]

I'm not sure if I confused the issue by initially posting the incorrect code, but this is the issue

The below code calculates profit based on the price in AO, which you can see above.

=IF(AO21<>"",IF(K21<>J21,(AO21-1)*1,-1),"0")

This code correctly adjusts the price of the price in AO based on 3 odds ranges

=IF(AO21<=3,AO21*1.1,IF(AO21<=10,AO21*1.15,IF(AO21>10,AO21*1.2,"0")))

The aim is to combine the 2 formulas so that profit is calculated, not off the base price in AO, but on the adjusted price.

So if J & K are equal, the result is a loss of 1. If J & K are not equal, then the profit calculation is preformed.

I hope that clarifies it somewhat

cheers