+ Reply to Thread
Results 1 to 13 of 13

Help understanding a formula

  1. 04-14-2013, 09:48 PM #1
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Help understanding a formula

    I am trying to learn or decipher a formula and I would appreciate it if someone would explain what each part of this formula is representing and meaning. I bought a meal planner online which uses Escel and I need to understand this formula so I can redo it because I want to add some things to the planner.

    There are two worksheets within this meal planner.
    One sheet is called "Nutrition Values"
    the other one called "Training Day"

    the formula is being used between both worksheets. The formula is:

    protein: VLOOKUP(D13,'NutritionalValues'!B9:F151,3,FALSE)* F13/100

    If you can please explain what all this means step by step I would really appreciate it.

    Thank you
    Reply With QuoteRegister To Reply
  2. 04-14-2013, 09:56 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help understanding a formula

    For this part:

    VLOOKUP(D13,'NutritionalValues'!B9:F151,3,FALSE)

    See this:

    http://contextures.com/xlFunctions02.html

    The above function is returning a numeric value that is getting multiplied by the value in cell F13 and is then divided by 100.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 04-14-2013, 10:01 PM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Help understanding a formula

    hi excel0124. it's a little hard to describe without your actual data but i suppose you wouldn't want to share something for free here since you bought it. i'll give it a go. i suppose the sheet with the formula is "Training Day". in "Training Day" sheet under D13, there's a value. let's call this ValueB. in "NutritionalValues" sheet, there are ValueA to maybe ValueZ in the range B9:B151. what the VLOOKUP does is find ValueB inside this range B9:B151. once found, it returns the 3rd column from column B on the same row ValueB is found as indicated here:
    VLOOKUP(D13,'NutritionalValues'!B9:F151,3,FALSE)

    the "FALSE" you see is to find an exact match of ValueB. that means it cannot be "Value B" or "Val B", etc. it must be exact or it'll return #N/A. it then takes the value found, multiplied by F13 & divided by 100. hope that helps.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  4. 04-14-2013, 10:02 PM #4
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help understanding a formula

    The vlookup takes the value in D13, finds a match to that value in B9:B151 on the NutritionalValues sheet.
    If the match is found, it returns the corresponding value from the 3rd column of the referenced array B9:F151.
    The 3rd column of B:F is D.
    So if the match to D13 is found in Say B75, then the vlookup returns the value from D75
    The FALSE is an option of the vlookup to make it search for an Exact Value or a "Closest Match".
    False means it searches for Exact Match.

    Once vlookup has the returned value, then it's simple math..Value multiplied by F13 then devided by 100


    HOpe that helps.
    Reply With QuoteRegister To Reply
  5. 04-14-2013, 10:06 PM #5
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    I will send a screen shot WITHIN five min of both pages. It wont affect anything because there is way to much detail to this program
    Reply With QuoteRegister To Reply
  6. 04-14-2013, 10:14 PM #6
    FDibbins
    FDibbins is offline
    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,929

    Re: Help understanding a formula

    Just fyi, a picture probably wont be much use, it cant be edited, and in all likely-hood, no-one can see how the references work.

    I also think that the explainations you already have, pretty much tell you what it's doing....
    it is taking the value in D13
    then searching for that value in a range that starts in B9, goes down to row 151 and across to column F......B9:F151
    when it finds the 1st match to the contents of D13 in column B, it is then returning the contents of that row, in the 3rd column from B...ie column E.
    The FALSE is to make sure it returns an exact match with D13
    VLOOKUP(D13,'NutritionalValues'!B9:F151,3,FALSE)
    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 With QuoteRegister To Reply
  7. 04-14-2013, 10:14 PM #7
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    Here is the Screen Shots for you.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  8. 04-14-2013, 10:30 PM #8
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    Thanks everyone, I will play with adding some more columns and redoing the formulas and see if they are working correctly. I appreciate the help
    Reply With QuoteRegister To Reply
  9. 04-14-2013, 11:31 PM #9
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    Ok I understand somewhat. I still need help on rewriting the formula. I made some changes on the "Nutritional Value" page. I inserted columns B-F (Grams per serv -Fat in the color of gray) The yellow section G- J now need to be the numbers that are transferred over to the "Training Day" sheet. I tried a few did changes to the formula but it is still taking 60g and 13g and I need 49.2 and 10.7 to transfer over as well as yellow carbs ans fats which are both currently 0. I attached the two pages to look at. On Nut Value sheet it is the Eggology in Row 16 that we are looking at, and in Training Day sheet it is Row 29 that we are needing the numbers transferring over too.

    Basically I don't want the "Training Day" sheet to use any of the numbers from columns C -G.
    Also.. I inserted formulas in the each cell in the new yellow section so I can get the correct totals I am needing for transferring over
    Attached Images Attached Images
    Last edited by excel0124; 04-14-2013 at 11:57 PM.
    Reply With QuoteRegister To Reply
  10. 04-15-2013, 02:32 PM #10
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    Understandable, I will try my best to explain. I have sheet A (Nutritional Values) and Sheet B (Training Day)

    In sheet B I have a formula in Cell H11 (For Protein) which is =VLOOKUP(D11,NutritionalValues!B6:F192,3,FALSE)* F11/100

    In sheet B I have another formula in Cell Jll for (carbs) which is =VLOOKUP(D11,NutritionalValues!B6:F192,4,FALSE)* F11/100

    The last formula in Sheet B cell R11 (Fats) is
    =VLOOKUP(D11,NutritionalValues!B6:F192,5,FALSE)* F11/100

    I have now added about 5 more columns in sheet A (Nut Value) so it isn't taking the correct columns any longer, it is still giving me the old totals I previously had. I still have the same original columns with the same numbers as before, but in the newly added columns I have formulas in place so it is now those new numbers or columns that I want to transfer over. With the new columns I would like the formula to take from columns (I, Protein) (J, Carbs) and (K, Fat)

    I really hope I am explaining myself correctly!
    Reply With QuoteRegister To Reply
  11. 04-15-2013, 02:35 PM #11
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help understanding a formula

    With your vlookup
    =VLOOKUP(D11,NutritionalValues!B6:F192,3,FALSE)
    it's that 3 that determines from which column a result will be returned from.

    It represents the 3rd column within the referenced range of B6:F192.
    So the 3rd column of B6:F192 is column D
    If you wanted to return data from column I instead..then you would change the 3 to 8
    column I is the 8th column within B6:F192

    Hope that helps.
    Reply With QuoteRegister To Reply
  12. 04-15-2013, 02:54 PM #12
    excel0124
    excel0124 is offline
    Registered User
    Join Date
    02-02-2013
    Location
    GRAND FORKS
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help understanding a formula

    Wow it's amazing how something so simple can seem so difficult. That fixed it. Thanks Jonmo1, I appreciate your taking the time to help me.
    Reply With QuoteRegister To Reply
  13. 04-15-2013, 02:56 PM #13
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Help understanding a formula

    Glad to help, thanks for the feedback.
    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)

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