New to libre calc need syntax help combining formulas

IWebb · November 9, 2022, 7:23am

Hi just trying to figure out how to write a formula.

So I just want to combine the 6 formulas into 1 but I am getting stuck. As I am new to spreadsheets I am not even sure if the IF function is what I should be using. I think what I am trying to do is “Test” if results are true or false then do a simple calculation and output the answer to profits. Then I guess once the first row is correct I can just auto fill the rest.

I have tried IF and OR and SUM and SUMIF and they seem somewhat work but not in their entirety.
I think there are a few ways to go about it so I am sort of going around in circles

//*  =IF(L2="Bonus Back",(J2*K2)*0)  *//
//*  =IF(L2="Bonus Win",J2*K2)  *//
//*  =IF(L2="Loss",(J2*K2)*-1)  *//
//*  =IF(L2="No Result",(J2*K2)*0)  *//
//*  =IF(L2="Placed",(J2*K2)-K2)  *//
//*  =IF(L2="Win",(J2*K2)-K2)  *//

So I tried
=IF(OR(IF(L2="Bonus Back",(J2*K2)*0),IF(L2="Bonus Win",J2*K2),IF(L2="Loss",(J2*K2)*-1),IF(L2="No Result",(J2*K2)*0),IF(L2="Placed",(J2*K2)-K2),IF(L2="Win",(J2*K2)-K2)))
but it doesn’t seem correct.

Any help is greatly appreciated
Thank you
Isaac

Version: 7.4.2.3 (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: CL

Wanderer · November 9, 2022, 7:52am

Let us start with quoting as this site is not displaying everything… Above I find stars to multiply, with triple quotes I see this converted to em-tags.

[quote="IWebb, post:1, topic:83921"]
=IF(L2=“Bonus Back”,(J2<em>K2)<em>0)
=IF(L2=“Bonus Win”,J2</em>K2)
=IF(L2=“Loss”,(J2</em>K2)<em>-1)
=IF(L2=“No Result”,(J2</em>K2)<em>0)
=IF(L2=“Placed”,(J2</em>K2)-K2)
=IF(L2=“Win”,(J2*K2)-K2)
[/quote]

IWebb · November 9, 2022, 8:01am

Ah yes I see, I did not notice that.

The formulas are also posted in the uploaded example in column Q with the multiply signs included.

Thanks for pointing this out

karolus · November 9, 2022, 8:41am

try th formula:

=CHOOSE(MATCH(L2;{"Bonus Back"|"No Result"|"Placed"|"Win"|"Bonus Win"|"Loss"};0);0;0;J2*K2-J2;J2*K2-J2;J2*K2;J2*K2*-1)

JohnSUN · November 9, 2022, 8:44am

@karolus I think you missed "Adjustment"?

Update Oh, sorry, you didn’t miss it, it was Isaac who didn’t say what to do with the Adjustment.
@IWebb Welcome! In your example, there is no formula option for the Adjustment value.

IWebb · November 9, 2022, 8:48am

Wow, Thanks!
Its a great help just knowing about choose and match

karolus · November 9, 2022, 8:48am

No not me. but the OP gives no advice what should happen with “Adjustment”

IWebb · November 9, 2022, 8:49am

I think I will be able to figure it out from here

JohnSUN · November 9, 2022, 8:55am

@IWebb Please note that there is another form of notation using the IFS() function:

=IFS(L2="Adjustment";0;L2="Bonus Back";0;L2="No Result";0;L2="Placed";J2*K2-J2;L2="Win";J2*K2-J2;L2="Bonus Win";J2*K2;L2="Loss";J2*K2*-1)

IWebb · November 9, 2022, 8:59am

Ahh this is also great!
Really appreciated