+ Reply to Thread
Results 1 to 15 of 15

too many cells have determining factors - how to use the ones with products

  1. #1
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    too many cells have determining factors - how to use the ones with products

    Working on a pricing calculator.
    I am terribly sorry in advance for any confusion and my lack of proper terms.
    I have a complex pricing calculator I have been working on and as soon as I feel like it is almost finished I get thrown a curve ball.
    I need to calculate a set of cells but two cells have a determining factor. If L10 is used this needs to be apart of the final sum butttt if I9 is used then that cell needs to be a part of the final sum...
    In the image where I9 (run charge) and I10 (other run charge) are connected to L10 - if L10 is used then the Sell price needs to use that cell if L9 is filled then that cell needs to be apart of the sell price calculation. Each cell in column I have formulas based on each of the cells in the table.
    Thanks in advance
    Sheet calc 2.PNG
    Last edited by Cnuessle; 04-08-2022 at 09:34 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    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.

  3. #3
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Re: too many cells have determining factors - how to use the ones with products

    I attached what I could. I do not follow what you're requesting I correct. In the post I stated which cells were in question and the issue I was having. Is there something I should have done differently? Thanks in advance.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    You attached a screenshot. I am asking you to attach the workbook, please.

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

  5. #5
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Red face Re: too many cells have determining factors - how to use the ones with products

    Ok - Thank you for explaining. I will try to post correctly - I think that was done right this time
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    Thanks. Much easier than squinting at a screenshot! Having a look now.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    Pleae update your forum profile as requested above.

    Does this do what you want?

    =MAX(I9:I10)/B12

    I'm finding your explanation a bit hard to follow ...

  8. #8
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Re: too many cells have determining factors - how to use the ones with products

    I have created a few posts here and am still learning, thank you for your patience in sharing the ways to properly post. I appreciate your time and knowledge. I quickly added color fill to the cells that I am having an issue connecting. I did not finish updating the "sell price" formula from the correction that need to be made for the total calculating procedure.. I did want to note where those two cells would be connecting though. The sell cell needs to know if the one is filled use it but if the other is filled use it and from there continue with the remaining equation to find the total sum... I hope that make clearer sense to my goal

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    OK, so either what I said before:

    =MAX(I9:I10)/B12

    or this:

    =IF(I9="",I10,I9)/B12

  10. #10
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Re: too many cells have determining factors - how to use the ones with products

    Yes.. So I currently have =IF((OR(I9>I10,I9<I10)), I10/B12 to determine what cell should be calculated... how would I make the sell price formula know which cell to use though so would this be the best practice =SUM(G3*I5/12/I6+I8+I11+I12+I13+C11) - I11 is the cell that has this new formula (determining cell) implemented... Sorry for my lack of proper verbiage.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    Put one or other of my formulae into L10. This is the cell that determines which of the two values to use. In your final calculation, you use just L10: you don't need to decide again because the choice is already made in L10.

    Please try it.

  12. #12
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Re: too many cells have determining factors - how to use the ones with products

    Ah sweet so what I stated above would be correct. Ok let me test this and I will return. I also update my excel info too.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    Let us know how you get on.

    Essentially, where there is a choice of values, you deal with that choice in a helper cell like L10. Then, when it comes to final calculations, you simply use the helper cells.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  14. #14
    Registered User
    Join Date
    01-27-2022
    Location
    Fl,usa
    MS-Off Ver
    365 apps for Enterprise
    Posts
    17

    Re: too many cells have determining factors - how to use the ones with products

    Great thank you I had an issue with the helper cell but I made the correction that was needed. Thank you for your help. I think this is handled now. I will mark resolved.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: too many cells have determining factors - how to use the ones with products

    Super! Well done.

+ 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. Replies: 8
    Last Post: 07-01-2016, 02:07 AM
  2. Replies: 6
    Last Post: 03-05-2016, 02:42 PM
  3. [SOLVED] Posting result based on factors from other cells
    By mikerules in forum Excel General
    Replies: 5
    Last Post: 11-07-2014, 02:26 AM
  4. [SOLVED] Count cells depending on multiple factors
    By coco1365 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-15-2014, 10:01 AM
  5. Determining prices for products based on competition
    By rajivs27 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-31-2014, 12:50 PM
  6. Replies: 6
    Last Post: 07-31-2009, 10:58 AM
  7. [SOLVED] Locating Trendline Equation Factors in Cells
    By Phil Hageman in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-03-2005, 10:06 AM

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