Forum Discussion
Referencing text in a cell to multiple another cell
Hello all,
I am fairly new to formulas in excel and was wondering if there is a formula that I can put in F14 that will reference the text in G14, "Lost", and then take the value of E14 and multiply it by 0. I have been searching for hours and continue to end up with the lovely parse error.
Ideally, I would like for the "Payout" to always reference the "Status" ("Lost, Win, Not Played") and multiply the "Bet" by either 10, for Win, or 0, for Lost.
5 Replies
emligh Try this in F14:
=E14*10*(G14="Win")
This trick here is that you always multiply the bet by 10 and then multiply by 1 or 0. The bit G14="Win" returns either TRUE or FALSE, and these represent either 1 or 0. Anything else then Win in G14 will result in 0.
I apologize for all the questions.
Is there a way to combine this formula from =F12*10*(H12="Win") to =F12*10*(H12="5/5 Win") =F12*2*(H12="4/5 Win") =F12*0.4*(H12="3/5 Win") all in the same cell?
I tried a handful but I cant seem to figure it out.
emligh It's not going to be pretty, but if you only have these three option you could use LOOKUP like this:
=F12*LOOKUP(H12,{"3/5 Win","4/5 Win","5/5 Win"},{0.4,2,10})
Riny_van_Eekelen That worked! Thank you very much! 🙂
Do you also know a way to make the “Bet” override the manual input of 5 to 0 when the “Status” reads Not Played?
emligh Thus the the bet is always 5 unless the status is "Not Played"?
If so, you can use a similar approach.
=5*(G14<>"Not Played")
