+ Reply to Thread
Results 1 to 5 of 5

Pricing: Referencing Tier by Brand to Suggested Sell

  1. 02-28-2019, 02:56 PM #1
    HavenS
    HavenS is offline
    Registered User
    Join Date
    02-27-2019
    Location
    San Francisco, CA
    MS-Off Ver
    Office 365, Business
    Posts
    4

    Question Pricing: Referencing Tier by Brand to Suggested Sell

    Hi Excel Wizards --

    My name is Haven, and I work at a lighting agency.

    Long story short, I need to build a tool to help others price things accurately to clients prior to formal quotation.

    Punchline: Can you help me with figuring out how to instruct Excel to accurately reference PRICE TIER & PRICE TIER TYPE by BRAND? Tiers are assigned to brands with coefficients per TIER TYPE. Think I can take it from there.

    START PRICE vs BRAND & PRICE TIER TYPE = SELL PRICE

    Here's how I'd like the flow to go.

    1-- User inputs START PRICE
    2-- User selects a BRAND from dropdown list
    3-- User selects a PRICE TIER TYPE from dropdown list
    4-- Excel transforms the START PRICE to get a NET PRICE based on START PRICE type from reference sheet
    5-- Excel references the PRICE TIER for chosen BRAND per selected PRICE TIER TYPE to get the proper coefficient to be applied to NET PRICE
    6-- Excel factors the START PRICE by the PRICE TIER for BRAND
    7-- SELL PRICE is output

    I'm able to do this manually in less than 5 seconds given a START PRICE and a target TIER with the calculator app on my phone, but telling Excel how to do the same thing turns out to be tricky. It's worth it, though.


    Thank you for your time, attention, and mental gymnastics.

    Take care and be well,
    ~Haven.-
    Reply With QuoteRegister To Reply
  2. 03-01-2019, 05:26 AM #2
    KOKOSEK
    KOKOSEK is offline
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,743

    Re: Pricing: Referencing Tier by Brand to Suggested Sell

    Are You able to create and upload any sample file, please? Add comments and expected result inside.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Reply With QuoteRegister To Reply
  3. 03-01-2019, 01:06 PM #3
    HavenS
    HavenS is offline
    Registered User
    Join Date
    02-27-2019
    Location
    San Francisco, CA
    MS-Off Ver
    Office 365, Business
    Posts
    4

    Re: Pricing: Referencing Tier by Brand to Suggested Sell

    Hi Kokosek --

    Attempted to write a more informative initial post and had also attempted to attach a dummy doc with examples, but was prevented from doing so as a new user to the forum.
    Have again been prevented from attaching/linking. Will try PMing the document. Frustrating.

    Suspect this can be solved with some combination of VLOOKUP, INDEX/MATCH, and IFS functions, just having trouble getting the (2) dropdown boxes to work as a set of conditional references.
    I could just write in the coefficients into (appx 200+ total) nested IF functions, but that's tedious, inelegant, and leaves me as the only one who can maintain this document.
    Want to be able to change the coefficients on the fly in a transparent way and add/remove brands as our line card changes.

    Thank you,
    ~Haven.-

    Note: Went back and double checked the math on the Tiers sheet; T5-T9 are calculated in error, apparently I built the document too hastily and didn't proofread it when I desensitized. :\ Sorry. Please forgive me. Even so, those errors will not affect the exercise itself.
    Attached Files Attached Files
    Last edited by HavenS; 03-01-2019 at 01:12 PM. Reason: Notes at bottom
    Reply With QuoteRegister To Reply
  4. 03-01-2019, 01:09 PM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,224

    Re: Pricing: Referencing Tier by Brand to Suggested Sell

    FYI - you will not be able to post links until you have posted a few more times, but, as you have discovered, you are allowed to post an attachment, which is all you need to be able to do.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 03-01-2019, 01:18 PM #5
    HavenS
    HavenS is offline
    Registered User
    Join Date
    02-27-2019
    Location
    San Francisco, CA
    MS-Off Ver
    Office 365, Business
    Posts
    4

    Re: Pricing: Referencing Tier by Brand to Suggested Sell

    Hi AliGW --

    Understood.

    If the previous attachment is too weak an example, I can put some more effort into it and revise with additional commentary and direction.
    By no means am I attempting to ask the forum to do my work for me in its entirety, just struggling to understand how to achieve this particular part of the overall goal.
    All I want to do is get the cells to reference the dropdowns appropriately.

    Please let me know if the previous was sufficient?

    Thank you kindly,
    ~Haven.-

    edit: Is there a character limit? Whenever I try to add more information, I get a "no links for you!" error, even though the post contains only text and numbers. :S
    Last edited by HavenS; 03-01-2019 at 01:31 PM. Reason: Question, char limit
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Water Consumption Tier Pricing
    By CascadeClerk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2018, 11:45 AM
  2. Tier pricing for data
    By jhereu93 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2018, 09:24 AM
  3. Tier pricing with few criteria to meet
    By Stephanie Tham in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2018, 03:56 AM
  4. Tier pricing
    By cbolin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2017, 02:51 AM
  5. Tier Pricing help
    By xcllog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-10-2015, 09:57 PM
  6. [SOLVED] Trying to compare current pricing and costs with suggested pricing
    By dearnne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2013, 07:01 PM
  7. tier pricing
    By billburr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2006, 02:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1