+ Reply to Thread
Results 1 to 9 of 9

Alternate cell substitution based on condition

  1. #1
    Registered User
    Join Date
    10-20-2017
    Location
    London, England
    MS-Off Ver
    Office 365 Business V1707
    Posts
    18

    Alternate cell substitution based on condition

    I have a total (cost) figure and want to calculate an allocation of that figure to a (cost) category. A user can specify by input the allocation by % or by absolute value. If they enter absolute value, I calculate %. If they enter %, I calculate absolute value. What's the simplest way to achieve this? Many thanks in advance.

    So Total Cost = X
    Category cost = Y% then Category cost, Z = X * Y%
    or
    Category cost = Z then Category cost Y% = 100 * Z / X

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Alternate cell substitution based on condition

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Alternate cell substitution based on condition

    Hi phodges,

    I assume you have three columns of data - X is a figure you determine somehow while Y and Z are are a calculation.

    If you are asking if you can have a formula which will allow users to enter data into either if these cells and get the answer in the other, you get into circular references and it won't work. You need to have the data entry in another place.

    Assume your user enters values int either AA for the %age or AB for the value:
    in Y1 enter =IF(AB1="",AA1,AB1/X1)
    in Z1 enter =IF(AB1="",X1*AA1,AB1)

    You are effectively using a "helper column" to get excel to provide what you are trying to achieve.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  4. #4
    Registered User
    Join Date
    10-20-2017
    Location
    London, England
    MS-Off Ver
    Office 365 Business V1707
    Posts
    18

    Re: Alternate cell substitution based on condition

    Hopefully I have attached sample spreadsheet as recommended. Thanks for responses so far. Hope attached makes it clear.
    There is the base case sheet which is copied twice to show the different desired outcomes.
    1. User specifies allocation of cost by % and Value is calculated, or
    2. User specifies allocation of cost by Value and % is calculated
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    Re: Alternate cell substitution based on condition

    The thing is, you can't have a formula in a cell and manual data entry in one cell - it's either/or. If you want this to happen in cells B3:C6, you are going to need VBA.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    10-20-2017
    Location
    London, England
    MS-Off Ver
    Office 365 Business V1707
    Posts
    18

    Re: Alternate cell substitution based on condition

    Thanks David - As per my example spreadsheet it's a bit trickier and looks like I need to rethink the requirement (not wishing to start writing VBA).

  7. #7
    Registered User
    Join Date
    10-20-2017
    Location
    London, England
    MS-Off Ver
    Office 365 Business V1707
    Posts
    18

    Re: Alternate cell substitution based on condition

    Thanks for the observation. I concur. I don't want to start writing VBA, so will have to rethink the requirement.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,093

    Re: Alternate cell substitution based on condition

    Me or David?

    Yes, you will need to rethink if you don't want to use VBA.

  9. #9
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Alternate cell substitution based on condition

    I think you are saying that you have ALMOST solved it, looking at your post.

    If you change the formula in H10 (and following) to =G3-G10 instead of =C3-G10, I think you will get what you want.

    I do question whether the Savings heading is correct, however. It seems to me that if the cost exceeds the Target Cost, it is NOT a saving.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


+ 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. Question - Substitution function is substitution its own values
    By debrad1207 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2018, 10:23 AM
  2. [SOLVED] Macro for alternate color based on cell value
    By Neilesh Kumar in forum Excel General
    Replies: 10
    Last Post: 08-10-2017, 05:15 AM
  3. [SOLVED] divide cell based on partial contents of alternate cell
    By LG1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-03-2013, 05:57 AM
  4. Replies: 3
    Last Post: 04-30-2012, 04:43 PM
  5. list alternate cell based off search function results
    By agragg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-03-2012, 05:41 PM
  6. Select worksheet based on condition & populate cell based on condition
    By beth1069 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2011, 01:27 PM
  7. [SOLVED] Excel 2007 : Alternate cell value based on different cells
    By tradersteve in forum Excel General
    Replies: 2
    Last Post: 09-22-2010, 05:52 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