Check Out Our Shop
Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Location
    West Coast of the East Coast
    Posts
    7,781

    Any Excel gurus out there?

    HELP!!!!

    I have a list of prices that I have created a VLookup to reference when quoting.
    When it hits a match, it works perfectly.

    When I apply a discount multiplier to the standard cost though, some items are out to the 3rd or 4th decimal place. So the Thousandths, or 10 thousandths place. My sheet only shows to the hundredths.

    I discovered this when my totals wouldn't add up on one sheet vs the other.

    Example-

    1000 units costing $26.40 at a .80 multiplier = (26.4 *.8) 1000 = $21,120

    But my sheet might show that $26.40 cost, but in the background it is computing a different number-
    Example using the hidden extra thousandths and 10 thousandths number.

    Say the hidden cost shows as $26.4089
    (26.4089*.8) 1000 = $21,127.12

    Do this enough times, and my pricing to my customer is way blown out compared to what I want to charge them.
    Usually not enough to lose the order, because we aren't dealing with those kind of numbers, but on a $100- $200,000 job, this could cost me the bid.

    Now I am super paranoid about it and triple checking all my numbers.

    So questions-
    A- Is there a way to set the reference pricing sheet to only bring the value out to the hundredths place, and round up?
    B- Is there a way to sort the column to find anything that is blown out past the hundredths place?

    There is way too much data here to scroll through and try to find the offending numbers.
    It is random- only certain ones have it set that way.

    Pretty typical of my company. They have different people doing the same task different ways. It nets you a mess, and I always seem to find it.

    Thanks for your help.

  2. #2
    Join Date
    Mar 2004
    Location
    West Coast of the East Coast
    Posts
    7,781
    Correction-

    It seems like the data I am pulling from is correct.

    When I apply the multiplier though, it is spitting out values that are not rounded up to the nearest hundredth-

    Example-
    Cost- $12.25
    Multiplier- .90
    Result- $11.025

    How do I get that result to spit out $11.03?
    There is already a formula in that cell, so I am assuming I have to round that result somehow?
    Just trying to figure out where the parenthesis should go, and what combo gets me the right result.

    Thanks

  3. #3
    Join Date
    Nov 2005
    Posts
    8,404
    Quote Originally Posted by warthog View Post
    Correction-

    It seems like the data I am pulling from is correct.

    When I apply the multiplier though, it is spitting out values that are not rounded up to the nearest hundredth-

    Example-
    Cost- $12.25
    Multiplier- .90
    Result- $11.025

    How do I get that result to spit out $11.03?
    There is already a formula in that cell, so I am assuming I have to round that result somehow?
    Just trying to figure out where the parenthesis should go, and what combo gets me the right result.

    Thanks
    =round(1.2345,2) should be 1.23

    =roundup(1.2345,2) should be 1.24

    Replace 1.2345 with the formula you have now.
    A woman came up to me and said "I'd like to poison your mind
    with wrong ideas that appeal to you, though I am not unkind."

  4. #4
    Join Date
    Mar 2004
    Location
    West Coast of the East Coast
    Posts
    7,781
    Nice, I had the round in the wrong place.
    I'll try that. Thanks

  5. #5
    Join Date
    Aug 2013
    Location
    shadow of HS butte
    Posts
    6,500
    Came here to say the same as Jono. That should fix your problem


    Sent from my iPhone using TGR Forums

  6. #6
    Join Date
    Mar 2004
    Location
    West Coast of the East Coast
    Posts
    7,781
    That worked, thanks guys.

  7. #7
    Join Date
    Jan 2006
    Location
    Carbondale
    Posts
    12,535
    Don't worry guys, I asked the ski bros how to do the excel thing... amazingly they are not all dentists.
    www.dpsskis.com
    www.point6.com
    formerly an ambassador for a few others, but the ski industry is... interesting.
    Fukt: a very small amount of snow.

  8. #8
    Join Date
    Dec 2012
    Location
    I can still smell Poutine.
    Posts
    24,979
    I hate Excel. Team I'm on now uses it way too much. I was used to connecting to my devices from the CLI and doing the heavy lifting in ksh or Bash using standard UNIX tools, dump it all to a CSV. The current environment sucks for that for many reasons including that they took away my Linux laptop and make me use Windows. Cygwin takes some of the edge off. I'm trying to get up to speed on openpyxl which would let me reassert some control over these bozos.

  9. #9
    Join Date
    Dec 2016
    Location
    In a van... down by the river
    Posts
    14,009
    Quote Originally Posted by riser4 View Post
    I hate Excel. Team I'm on now uses it way too much. I was used to connecting to my devices from the CLI and doing the heavy lifting in ksh or Bash using standard UNIX tools, dump it all to a CSV. The current environment sucks for that for many reasons including that they took away my Linux laptop and make me use Windows. Cygwin takes some of the edge off. I'm trying to get up to speed on openpyxl which would let me reassert some control over these bozos.
    I've been using Windows Subsystem for Linux and it has also "taken the edge off" of simple shit I can do in Linux that flummoxes me in Powershell. But, to be honest, I totally suck at Powershell and have zero interest in trying to learn all that shit.

  10. #10
    Join Date
    Dec 2012
    Location
    I can still smell Poutine.
    Posts
    24,979
    WSL and WSL2 don't work well with some of the customer VPNs. Cygwin doesn't care.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •