Results 1 to 8 of 8

Multiple IF's to calculate price based on cost and add handling charge and round price

Threaded View

  1. #1
    Registered User
    Join Date
    10-13-2014
    Location
    Daphne, AL USA
    MS-Off Ver
    2007
    Posts
    4

    Red face Multiple IF's to calculate price based on cost and add handling charge and round price

    I am working on following formula but it does not work correctly.

    =ROUND(IF(H1<10,H1*2.2+12.5,IF(H1>10,H1*2+10,IF(H1>50,H1*1.8+10,IF(H1>100,H1*1.7+10,IF(H1>150,H1*1.6+10,IF(H1>200,H1*1.55+10)))))),0)-0.03

    What I am trying to accomplish is: H1 is the cost of an item and the formula is to automatically calculate the selling price, add shipping/handling, and round the price to end in .97


    If H1 is less than 10 then multiply by 2.2 and add 12.5

    If H1 is 10 but less than 50 then multiply by 2 and add 10

    If H1 is 50 but less than 100 then multiply by 1.8 and add 10

    If H1 is 100 but less than 150 then multiply by 1.7 and add 10

    If H1 is 150 but less than 200 then multiply by 1.6 and add 10

    If H1 is 200 or greater then multiply by 1.55 and add 10

    THEN round to nearest .00 and subtract 0.03 so ending number ends in .97


    Any help with fixing this formula is so greatly appreciated. It is probably something simple but my mind is blind to it.

    ~ Roy
    Attached Files Attached Files
    Last edited by RoyRose; 10-13-2014 at 12:36 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. calculate retail price based on mark up on cost price
    By pvl in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-27-2014, 03:49 AM
  2. [SOLVED] Setting a selling price based on cost price and order quantity
    By Steven811 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 08:07 AM
  3. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  4. [SOLVED] Need to calculate retail price based on known cost and percentage
    By rayzorkat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2013, 06:27 PM
  5. Replies: 4
    Last Post: 08-15-2012, 09:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1