+ Reply to Thread
Results 1 to 6 of 6

A cell holding a value with other cells subtracting from it...and a caveat

  1. #1
    Registered User
    Join Date
    12-04-2015
    Location
    Longview, TX
    MS-Off Ver
    Two Thousand and Ten
    Posts
    3

    Question A cell holding a value with other cells subtracting from it...and a caveat

    Hello, first time poster here and hoping to get some help with something outside of my wheelhouse !

    I am designing an P&P rpg and using Excel to house my character sheets. I'm capable enough in excel to handle the small formulas and referencing but this one formula is a bit over my head.

    What I desire is as follows - A23 will contain the amount of character experience points not yet distributed. A3, A7, A11, and A15 will house the character attributes that I wish to subtract their value from A23.

    The hold up that I'm having is that I need for the values of A3,A7,A11, and A15 to subtract from A23, not their displayed values but a value dependant on the number in the cell.

    The first 5 points in an A3, A7, A11, and A15 cell only cost 1 point each. Once you reach 6, however, each increase costs 2 points. This pattern continues into infinity with every 5th value increasing the cost of the points by 1. Examples below.

    Example 1 : If a character has a value of 7 in their A3 stat then I need 9 points subtracted from A23.
    Example 2 : If a character has a value of 3 in their A7 stat then I need 3 points subtracted from A23.
    Example 3 : If a character has a value of 22 in their A11 stat then I need 60 points subtracted from A23.


    Here is an example of the grid that may shed more light on the system that is being used.

    1 1
    2 1
    3 1
    4 1
    5 1
    6 2
    7 2
    8 2
    9 2
    10 2
    11 3
    12 3
    13 3
    14 3
    15 3
    16 4

    This is my first post so if it's vague or more information is required please let me know and thanks in advance!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A cell holding a value with other cells subtracting from it...and a caveat

    Hi, welcome to the forum

    Are you saying that you want A23 to have a certain value (not a formula), but then to not show that value, but to show a different value, based on some criteria?
    If so, not possible with formulas - a cell can contain either a value, OR a formula, not both - and to have the cell contain 1 thing, but show another, would require a formula (or VBA)

    The hold up that I'm having is that I need for the values of A3,A7,A11, and A15 to subtract from A23, not their displayed values but a value dependant on the number in the cell.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-04-2015
    Location
    Longview, TX
    MS-Off Ver
    Two Thousand and Ten
    Posts
    3

    Re: A cell holding a value with other cells subtracting from it...and a caveat

    Thank you for the quick response.

    That is a good point. Upon reviewing it I believe that A23 should contain the forumla that compiles the cost of A3,A7,A11, and A15 and displays them.

    Does that help with clarity ?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A cell holding a value with other cells subtracting from it...and a caveat

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like? Im having a hard time relating your cell ref's to your explanation

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  5. #5
    Registered User
    Join Date
    12-04-2015
    Location
    Longview, TX
    MS-Off Ver
    Two Thousand and Ten
    Posts
    3

    Re: A cell holding a value with other cells subtracting from it...and a caveat

    CHARSHEETfnl.xlsx

    Additionally, the cells AI18,AI13,AI9, and AI5 would each add a value of 5 to A23 if they have any value at all. This is not necessary but certainly would be ideal !

    I highlighted the pertinent cells as red text.

  6. #6
    Registered User
    Join Date
    07-01-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: A cell holding a value with other cells subtracting from it...and a caveat

    Hi Excell Dice..

    you can use below formula to get substracted qty from AP cost for each value of ability score

    =(((POWER((ROUNDUP([ability score]/5,0))-1,2)+((ROUNDUP([ability score]/5,0))-1))/2)*5)+((ROUNDUP([ability score]/5,0))*([ability score]-(5*((ROUNDUP([ability score]/5,0))-1))))


    Best Regards
    JRD

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Subtracting Range of Cells depending on Another Cell's Value
    By abedour in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2015, 10:32 AM
  2. [SOLVED] Cells adding values from another cell and holding that value
    By Campbound in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2013, 06:25 PM
  3. [SOLVED] Subtracting multiple cells from one cell
    By exceldummy01 in forum Excel General
    Replies: 4
    Last Post: 10-23-2012, 09:57 AM
  4. Subtracting from multiple cells using a remote cell
    By androo829 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2012, 06:33 PM
  5. copying and holding certain cells in the function
    By rwong520 in forum Excel General
    Replies: 1
    Last Post: 07-28-2006, 01:25 PM
  6. [SOLVED] Holding some cells and sliding others
    By Dan Shelby in forum Excel General
    Replies: 2
    Last Post: 01-26-2006, 01:40 PM
  7. Cells not holding format
    By Aurora in forum Excel General
    Replies: 0
    Last Post: 03-09-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1