+ Reply to Thread
Results 1 to 4 of 4

tricky formula required depending on numerical data per headers

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,086

    tricky formula required depending on numerical data per headers

    I am looking for a more efficient formula as I have to this manually due to different criteria per product tariff
    see attached
    very tedious when you have 1000 to 2000 rows of data

    that is quantity *packing*product litrage =litres header but if packing header is 0 same multiplication formula in litres header gives 0.

    So I have to make this manually quantity *product litrage =litres header

    A better formula to do this

    2.if headers packing,product litrage empty then show nothing in litres header

    3.A bit tricky if litres header has value and if sugar g/l has a value then multiply litres header *sugar g/l for sugar header to be filled otherwise if sugar g/l is bLANK BUT %ALCOHOL header has a value then * litres headers put value alcohol .

    4. if packing, product litrage , sugar g/l ,% alcohol headers are blank, show nothing in litres ,sugar and alcohol headers .

    Summary If there is data is sugar g/l header then multiply by litres header add value in sugar header otherwise if sugar g.l header is blank but % alcohol has value then * multiply by litres header add value in alcohol header.

    if column D to G IS EMPTY , do nothing show blank column I to K .
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: tricky formula required depending on numerical data per headers

    Hi -

    I think I followed your instructions as best I can interpret them. Please see the attached spreadsheet. Basically the formulas test to see if there are values in the Packing, Product Litrage and Qty columns and if so, execute the multiplication. I had to add some values to Packing as most of them were blank, which yielded a product of zero. I used a custom format to show numbers with a comma and zeros as a "-". Since the Sugar and Alcohol columns depend on numbers being in the Litres column, I didn't want to insert a blank. However, if you are absolutely set on blanks, then use conditional formatting so that any zero value just doesn't display.

    Hope this helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,086

    Re: tricky formula required depending on numerical data per headers

    Hi

    Thanks , exactly what I was looking for.

  4. #4
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: tricky formula required depending on numerical data per headers

    Great! Glad I could help. Thanks for the Rep!

+ 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: 1
    Last Post: 05-31-2016, 02:19 AM
  2. Help required with Macro to Copy Data based on Column Headers - please!
    By Andycostin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-02-2014, 05:45 PM
  3. Replies: 3
    Last Post: 10-08-2014, 04:17 PM
  4. [SOLVED] Simple numerical data formula, text shown to change depending on number
    By tasmith7482 in forum Excel General
    Replies: 14
    Last Post: 06-23-2014, 08:43 AM
  5. Required a MACRO for Pulling data from different workbooks with selected headers
    By yadiki prathap in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2014, 06:14 AM
  6. Macro to autofill a number of cells depending on a set numerical value in A1
    By dowell89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2013, 06:24 AM
  7. Help required for a tricky problem(s)
    By zybatu01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-03-2008, 12:19 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