+ Reply to Thread
Results 1 to 7 of 7

Can't find the correct formula

  1. #1
    Registered User
    Join Date
    03-10-2022
    Location
    Reading, PA, USA
    MS-Off Ver
    365
    Posts
    1

    Can't find the correct formula

    Hi, I have what I hope is a simple question. I have attached a simplified version of what I am trying to do. In this first part I have four columns. Under the headers of the first three columns are drop down from a list. Each of the choices in the drop down has a corresponding point value to it.

    As each drop down is selected, I want each of those point values to be added together and shown in the fourth column on the spreadsheet. Creating a drop down is not hard, adding the numbers together is not hard, what I don’t know is how to give an “alias” and say if Red in this cell then 10, but if green then 20, etc. I tried looking around at the formulas but can't seem to find what I am looking for. If anyone can point me in the right direction it would be great.

    I attached a snippet of an excel file.

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Can't find the correct formula

    what I don’t know is how to give an “alias” and say if Red in this cell then 10, but if green then 20, etc
    this can be managed by creating a table of your items and their corresponding values, then in the fourth column you can refer to the dropdown value against the table you created (using INDEX MATCH or VLOOKUP)
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Can't find the correct formula

    For example, using the values in the "Tables" sheet:

    =VLOOKUP(C7,Tables!$D$6:$E$8,2,0)+VLOOKUP(D7,Tables!$G$6:$H$8,2,0)+VLOOKUP(E7,Tables!$J$6:$K$8,2,0)

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Can't find the correct formula

    worksheet or Tab name : Sheet1

    Cell F7 formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by wk9128; 03-10-2022 at 06:22 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Can't find the correct formula

    @wk9128 that's nice and neat

    Although potential error if you have any values repeated in different tables (obviously not in the example sheet).

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,318

    Re: Can't find the correct formula

    @nick.williams Repetitions still add up , Please take a look at the attachment
    Attached Files Attached Files

  7. #7
    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: Can't find the correct formula

    Rather than tell us what doesnt work, please modify your thread title to something that actually describes your problem

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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

+ 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. [SOLVED] I need some help to find a correct formula
    By joericons in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2021, 09:57 AM
  2. Lookup Formula to find correct value & update
    By Poison990 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2017, 08:21 PM
  3. [SOLVED] Formula to find the correct tab
    By Andyc6 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-27-2017, 10:54 AM
  4. Can't find correct formula
    By Lele514 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-14-2016, 06:28 AM
  5. Please help me find a correct formula using multiple sheets
    By Bheybylyn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2014, 12:15 AM
  6. Trying to Find the Correct Formula
    By lago3525 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2012, 06:23 PM
  7. Can not find the correct formula
    By Robert Cortese in forum Excel General
    Replies: 1
    Last Post: 06-28-2012, 02:48 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