+ Reply to Thread
Results 1 to 7 of 7

Inventory Price Calculation

  1. #1
    Registered User
    Join Date
    02-08-2007
    Location
    Maine, USA
    MS-Off Ver
    Office 365
    Posts
    82

    Inventory Price Calculation

    I am doing a price increase calculation comparisons and using scenario manger.
    I have written the formula below. This works until it hits a value greater than 800.00. (Attachment has example)

    =IF($B882<=50,($B882*H$3),IF(AND($B882>50,$B882<=100),($B882*I$3),IF(AND($B882>=101,$B882<=200),($B882*J$3),IF(AND($B882>=201,$B882<=300),($B882*K$3),IF(AND($B882>=301,$B882<=500),($B882*L$3),IF(AND($B882>=501,$B882<=1000)*($B882*M$3),IF(AND($B882>=1001,$B882<=2000)*($B882*N$3),IF(AND($B882>=2001,$B882<=3000)*($B882*O$3),IF(AND($B882>=3001),$B882*P$3)))))))))

    I can not seem to find the problem ..... would appreciate any assistance.
    Attached Files Attached Files

  2. #2
    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: Inventory Price Calculation

    Try

    Please Login or Register  to view this content.
    Last edited by JohnTopley; 12-31-2016 at 11:04 AM.

  3. #3
    Registered User
    Join Date
    02-08-2007
    Location
    Maine, USA
    MS-Off Ver
    Office 365
    Posts
    82

    Re: Inventory Price Calculation

    This formula worked !! Why did the IF(AND() not work above 800.00?? Were the IF(AND()'s not necessary???

    How do I list this as solved?? Poor memory

  4. #4
    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: Inventory Price Calculation

    =IF($B882<=50,($B882*H$3),IF(AND($B882>50,$B882<=100),($B882*I$3),IF(AND($B882>=101,$B882<=200),($B882*J$3),IF(AND($B882>=201,$B882<=300),($B882*K$3),IF(AND($B882>=301,$B882<=500),($B882*L$3),IF(AND($B882>=501,$B882<=1000)*($B882*M$3),IF(AND($B882>=1001,$B882<=2000)*($B882*N$3),IF(AND($B882>=2001,$B882<=3000)*($B882*O$3),IF(AND($B882>=3001),$B882*P$3)))))))))

    The above had "*" rather than ","

    The ANDs are not necessary: look at the IF statements and you will see (work out!) that the AND is implied.

    e.g Value of 150 is not less than 50, not less 100 BUT is less than 200 i.e lies between 101 and 200.


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

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Inventory Price Calculation

    This is how I would solve it:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    As you can see I changed the first couple of values for the purpose of testing and I'm too lazy to fix it back.
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Inventory Price Calculation

    Why did I not do like this instead? My brain is full of glitches and ****...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-08-2007
    Location
    Maine, USA
    MS-Off Ver
    Office 365
    Posts
    82

    Re: Inventory Price Calculation

    Thanks for the help and the explanation!!!

+ 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: 2
    Last Post: 06-15-2015, 01:48 AM
  2. [SOLVED] Updating Inventory Price Changes between two Sheets
    By DanCurran1 in forum Excel General
    Replies: 5
    Last Post: 09-04-2014, 12:26 PM
  3. Hi I need help with inventory price change
    By princess45 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-27-2013, 03:46 AM
  4. Tough Inventory Weighted Average Price Problem, Please Help!
    By 1337 Ninja in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2013, 10:15 PM
  5. Master inventory product price changes with new order
    By Mmurra22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2013, 09:13 PM
  6. [SOLVED] Need remaining average inventory price after partial inventory is sold
    By Akano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 04:12 AM
  7. Supplier Inventory Best Price Analsysis
    By pilkingtond in forum Excel General
    Replies: 0
    Last Post: 08-27-2010, 05:16 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