Help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2207654%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20am%20pretty%20much%20a%20beginner%20to%20excel%20and%20formulas%2C%20so%20I%20was%20wondering%20if%20anyone%20out%20here%20knew%20about%20a%20formula%20that%20would%20work%20for%20me%2C%20or%20if%20its%20not%20a%20possibility%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20hoping%20to%20accomplish%20is%20to%20be%20able%20to%20type%20in%20either%20a%20%22y%22%20or%20a%20%22n%22%20into%20a%20cell%2C%20and%20then%20have%20that%20cell%20produce%20a%20number.%20The%20number%20I%20would%20like%20it%20to%20produce%20is%20predicated%20by%20the%20cell%20above%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20the%20cell%20in%20B2%20has%20a%20%225S%22%20in%20it%2C%20and%20I%20type%20a%20%22y%22%20into%20cell%20B3%2C%20I%20would%20like%20for%20cell%20B3%20to%20return%20a%20number%20like%20%225T%22%2C%20where-as%20if%20I%20type%20in%20a%20%22n%22%20into%20cell%20B3%2C%20I%20would%20like%20for%20B3%20to%20return%20a%20number%20such%20as%20%224v%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20limited%20knowledge%20of%20excel%2C%20in%20that%20I%20know%20I%20could%20input%20all%20the%20values%20I%20would%20like%20for%20it%20to%20return%20on%20another%20sheet%20if%20need%20be%2C%20but%20I%20am%20unsure%20of%20whether%20or%20not%20that%20would%20be%20helpful%2Fnecessary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20or%20help%20on%20this%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2207654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2207846%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F996422%22%20target%3D%22_blank%22%3E%40Iron1369%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20would%20be%20better%20to%20use%20a%20different%20cell%20for%20the%20result%3A%20if%20you%20enter%20specific%20values%20in%20B2%20and%20B3%2C%20a%20formula%20can%20return%20the%20resulting%20value%20in%20-%20for%20example%20-%20B4.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20provide%20some%20details%20about%20which%20values%20can%20be%20entered%20in%20B2%20and%20B3%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2207956%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2207956%22%20slang%3D%22en-US%22%3EOk%2C%20so%20my%20friends%20and%20I%20play%20some%20tabletop%20games%2C%20for%20which%20we%20have%20various%20dice%20that%20we%20use%20for%20different%20things.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20values%20I%20would%20like%20to%20be%20shown%20are%20things%20like%20%222d12%2B1d6%22.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20Dice%20will%20go%20up%20or%20down%20if%20the%20players%20succeed%20or%20fail%20at%20something.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%20if%20person%20a%20succeeds%20at%20a%20task%2C%20and%20they%20are%20currently%20at%20%222d12%2B1d6%22%2C%20I%20would%20like%20for%20the%20number%20for%20them%20to%20increase%20to%20%222d12%2B1d8%22%2C%20whereas%20if%20they%20failed%2C%20the%20number%20would%20decrease%20to%20%222d12%2B1d4%22.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20I%20would%20like%20to%20be%20able%20to%20just%20enter%20a%20%22y%22%20or%20%22n%22%20to%20say%20whether%20or%20not%20they%20succeeded%2C%20and%20then%20have%20the%20result%20go%20either%20up%20one%20die%20size%2C%20or%20down%20one%20die%20size%20accordingly.%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20that's%20understandable%20enough.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you.%3C%2FLINGO-BODY%3E
New Contributor

Hi all, I am pretty much a beginner to excel and formulas, so I was wondering if anyone out here knew about a formula that would work for me, or if its not a possibility at all.

 

What I am hoping to accomplish is to be able to type in either a "y" or a "n" into a cell, and then have that cell produce a number. The number I would like it to produce is predicated by the cell above it.

 

For example, if the cell in B2 has a "5S" in it, and I type a "y" into cell B3, I would like for cell B3 to return a number like "5T", where-as if I type in a "n" into cell B3, I would like for B3 to return a number such as "4v".

 

I have a limited knowledge of excel, in that I know I could input all the values I would like for it to return on another sheet if need be, but I am unsure of whether or not that would be helpful/necessary.

 

Any advice or help on this would be greatly appreciated.


Thank you!

7 Replies

@Iron1369 

It would be better to use a different cell for the result: if you enter specific values in B2 and B3, a formula can return the resulting value in - for example - B4.

 

Could you provide some details about which values can be entered in B2 and B3?

Ok, so my friends and I play some tabletop games, for which we have various dice that we use for different things.

The values I would like to be shown are things like "2d12+1d6".

The Dice will go up or down if the players succeed or fail at something.

For example if person a succeeds at a task, and they are currently at "2d12+1d6", I would like for the number for them to increase to "2d12+1d8", whereas if they failed, the number would decrease to "2d12+1d4".

So I would like to be able to just enter a "y" or "n" to say whether or not they succeeded, and then have the result go either up one die size, or down one die size accordingly.

Hopefully that's understandable enough.

Thank you.

@Iron1369 

Sounds complicated. Do you have a list of possible values?

So the list would be:
0, 1d4, 1d6, 1d8, 1d10, 1d12, 1d12+1d4, 1d12+1d6, 1d12+1d8, 1d12+1d10, 2d12, 2d12+1d4, 2d12+1d6, 2d12+1d8, 2d12+1d10, 3d12, 3d12+1d4, 3d12+1d6, 3d12+1d8, 3d12+1d10, 4d12.

That would be the whole range.

Thanks again!

@Iron1369 

Thanks.

See the attached sample workbook. It is a macro-enabled workbook so you'll have to allow macros when you open it.

To inspect the VBA code, right-click the sheet tab and select 'View Code' from the context menu. I have added comments to the code; this should help if you want to modify it.

@Hans Vogelaar 

Ok, sorry for the delay, work got busy and I forgot to check back on here.

 

I see the pull down with the options next to it on the excel file, but if I select either "y" or "n", nothing changes.


Not sure what I am doing wrong, any thoughts?

@Iron1369 

Make sure that you open the workbook in Excel for Windows or MacOS. VBA doesn't work in Excel for Android or iOS, nor in the browser version.

And make sure that you allow macros when you open the workbook.