Need help with conditional sums/formulas

[INEEDHELPGUY]

Hi everyone,

I'm a huge noob so help would be really appreciated.

I'll try to explain this as clearly as possible but it's a little confusing:

I have a column (column F) that is either W or L. Column G is a multiplier that differs on each row. Column J is the amount invested. Column K is Return. Column L is profit. Column M is return on amount invested %.

Here's what I want to do:

I want to have column K (Return), column L (profit) and column M (ROI %) be automatically calculated from column F (W or L: W means the multiplier and the amount invested will be multiplied together; but a L means that the amount invested is just multiplied by 0), column G (multiplier) and column J (amount invested).

For example, let's say that the following are entered: F2 is a W, G2 (multipler) is 2, and column J is $100, I want to be able to get column K - the return which should be =sum(G2*J2); column L - profit which should be =sum(K2-J2); and column M - the return on investment % which should be =sum(K2/J2).

So basically, the equation should be contingent on column F: if F2 is "W", then we do the multiplications. If F2 is "L" then column J is just multiplied by 0. How would I write out this formula to encompass the above?

Also, how do I write 1 formula for each column while having the column reflect its row? Specifically, I want J2 to be multiplied by G2, J3 multiplied by G3 and so on.

Thanks!

[Mallycat]

please post a sample sheet

[INEEDHELPGUY]

sampleforforum.xlsx

please post a sample sheet

Hopefully this clears it up a little.

So in column 3, because it's a "W" under F3, I want to be able to input only the "Line" (G3) and "Invested" (J3) and have Return, Profit and ROI% be calcuated.

In column 2, because it's a "L" under F2, I want to have "Invested" (J2) multiplied by 0 for K2.

Also, how do I do the formula so that it encompasses the whole column but also does it for the correct row - for example, Row 3 of J corresponds to Row 3 of G and so on.

[Mallycat]

Man, you are really making this complex. I want to help you but it is very difficult.


So when you say column 3, I assume you mean row 3. When you say "W" under F3, I assume yoy mean "W" in cell F3.

So if I can interpret what I think your realy problem is, it is as follows.

You make bets. When you place a bet, you will enter the line in column G and the amount invested in Column J.
Then later, you will find out if it wins or loses, and then you will place an L or W in column F. Then you want the spreadsheet to calculate the Return, profit and ROI.

Here are the formulas that should do this.

Cell K2 =IF(F2="W",G2*J2,IF(F2="L",0))
Cell L2 =IF(F2="W",K2-J2,IF(F2="L",-J2))
Cell M2 =IF(F2="W",K2/J2,IF(F2="L",0))

Copy the above formulas into the cells indcated, and then copy them down to the other cells.

[CliffB382]

This not really complex - try IF(F2="W",G2*J2,0)

This is IF F2 = W then G2*J2 otherwise 0 (because anything x 0 is 0

[INEEDHELPGUY]

Thanks so much to both of you. Because of the help, I was able to work a few other formulas into the spreadsheet which will save me a ton of time.