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.

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

Quote:

---

Originally Posted by warthog

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.

Nice, I had the round in the wrong place.
I'll try that. Thanks

Came here to say the same as Jono. That should fix your problem


Sent from my iPhone using TGR Forums

That worked, thanks guys.

Don't worry guys, I asked the ski bros how to do the excel thing... amazingly they are not all dentists.

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.

Quote:

---

Originally Posted by riser4

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. :D

WSL and WSL2 don't work well with some of the customer VPNs. Cygwin doesn't care.