Ask Your Question
0

Making Text Equal a number or Add a Value to another Number with Text.

asked 2019-05-01 05:54:26 +0100

CSTIG gravatar image

Been trying to figure out how to do this for sometime, but no functions or formulas have worked.

I have a Selection box, I7, that is simply YES or NO. If the User chooses YES then I need a way to add 50 to another cell, I4, and if they select NO then no values are changed or added.

There are two ways I have thought about doing this. One is more straightforward by trying to set up I4 to add the 50 on top of it's base value if I7 Equals "YES". I haven't been able to to get a Formula to work, Because I'm not sure how to set it up, usually getting Error 509-512 or it not recognizing the Value or Name.

The second idea was setting up a dummy cell to hold the value needing to be added to I4. So something along the lines of I7="YES" then J7="50". Then I can just easily have I4+I7 and if I7="NO" then no value will appear in J7 allowing I4 to just be the base value inputted. Problem again is I'm not sure how to set it up.

So anyone can help figure out one of these methods, or both, or another way to accomplish what I'm trying to do it would be much appreciated.

edit retag flag offensive close merge delete

Comments

(I don't exactly understand the subject as worded here.)
What, exactly, is the meaning of your term "selection box"? (Form Control? Something related tro Data>Validity?)
Spreadsheet cells are not history aware. Therefore you have no reliable way to have a formula calculating something refering back to the current/previous value.
Workarounds based on self-reference are not reliable due to the problem to exclude repeated recalculation. Basically: The formula doesn't know if it already was evaluated (Once regarding the current trigger!) or not.
If the action shall be triggeried by actual editing of cell(s) (SheetEvent Content changed) you can get something like an "ActionCounter" (what basically is what you want) by calling user code ("macro").

Lupp gravatar imageLupp ( 2019-05-01 08:49:01 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-01 14:03:53 +0100

CSTIG gravatar image

Figured it out by looking through someone else's document. By experimenting with the IF() function I used my second idea, the dummy/placeholder cell idea. When I type =IF(H44="YES",$'Skills and Attributes'.B18,IF(H44="NO","0")) it allows the cell, H43 in this instance, to add the value of $'Skills and Attributes'.B18 if the placeholder cell says YES and if it says NO a zero is placed instead.

So now I can prevent or allow values with numbers or text which helps prevent unnecessary values being added to certain equations under certain conditions.

edit flag offensive delete link more

Comments

The Not-Equal-"YES"-Expression is lacking the Not-Equal-"NO"-Part. What would you expect to get if H44 returns "Maybe"?
If you can assure that H44 is "NO" if not "YES", you should use
=IF(H44="YES"; $'Skills and Attributes'.B18; "0"). (This if you actually want the alternative result to be the text "0"). If you don't want to make this assurance, you should use something like
=IF(H44="YES"; $'Skills and Attributes'.B18; IF(H44="NO"; "0"; "ThisAlternativeResult")).

Lupp gravatar imageLupp ( 2019-05-01 16:48:31 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-01 05:54:26 +0100

Seen: 38 times

Last updated: May 01