+ Reply to Thread
Results 1 to 5 of 5

Help understanding formula please.

  1. #1
    Registered User
    Join Date
    02-22-2018
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    23

    Help understanding formula please.

    Hi,
    I have been following instructions found on a website for developing an MRP system which up until now has been great. However I have come unstuck with the following formula =IF(RIGHT(B2,5)="Total",0,G1+F2-E2) this code is placed in G2 in the following example. I need help understanding what each part of the formula does so I can adapt it for my own spreadsheet.

    Any help would be greatly appreciated.

    Screenshot 2018-02-22 at 18.28.30.png

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Help understanding formula please.

    well, IF starts an if this then that chain, the RIGHT(B2,5) says to the formula to look in cell B2 and go over 5 spaces from the right end of the cell and return those 5 letters/numbers and so if the right 5 letters (in this case) come out as "Total," return 0, otherwise take the value in G1, add F2 to it and subtract E2 from that.
    hope that helps.

    EDIT: it is all part of an if then statement, if then statements are like this... if this condition exists do this, otherwise do that.
    so if right b2,5 equals total, put 0 in this cell, otherwise take G1 and add F2 to it and subtract E2 from it.
    Last edited by Sam Capricci; 02-22-2018 at 02:46 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Help understanding formula please.

    =IF(RIGHT(B2,5)="Total",0,G1+F2-E2)

    It checks the rightmost 5 characters in the text in B2 (or Bn as the formula is dragged down the column) for the text "Total": if found (Matched) then set value in G2 (Gn) to zero. An example is B13 which contains "Base Shampoo Total" so G13 =0

    Otherwise add G1 to F2 and subtract E2; again references change as formula is dragged down column G.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Help understanding formula please.

    Hi DB and welcome to the forum,

    The Right(B2,5)="Total" is the first thing that is done. So if the formula is in G2, it would look at B2 to see if it ends with the word "Total" and if it does the true part of the if statement is done and if not, the false part of the if statement would be performed. THE problem is that in the above, you have merged cells from B2 down to B14. Having MERGED Cells, screws the whole thing up. We Excel Guru's hate Merged cells and your problem is a sample of why. Remove the merged cells and see if the word Total appears in B2, B3.. B14. If not then your formula won't work down the G column.

    I hope this helps you with your problem.
    Last edited by MarvinP; 02-22-2018 at 02:52 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    02-22-2018
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Help understanding formula please.

    Thanks everyone for your comments much appreciated.

+ 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. Need Help understanding Formula.
    By BlackAtlas in forum Excel General
    Replies: 6
    Last Post: 04-06-2016, 02:59 PM
  2. Understanding a formula
    By mbauman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2015, 09:02 PM
  3. Understanding a formula
    By mbauman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2015, 02:03 PM
  4. Formula is not understanding me
    By Shainal.Sutaria in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 07:56 AM
  5. NOT UNDERSTANDING THE FORMULA
    By PAPABEAR2252 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2005, 08:07 PM
  6. [SOLVED] Understanding a formula
    By Jordan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-26-2005, 05:15 PM
  7. Understanding this formula
    By Sal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2005, 03:06 PM

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