Excel formula help needed

I’m running into a bit of a headache with my formulas... I won't bore you with all the tedious details just yet...



Basically, whenever I try to input a 0% discount, the whole thing just spits out an error message. I’m not exactly clueless—I realize it's because the math is trying to divide by zero, which is obviously a non-starter—but I haven't quite figured out how to write a formula that actually accounts for that and keeps things running smoothly without breaking...

Thanks in advance!!!!

Well, obviously it’s because you’re dividing by zero.

You gotta set it up so that when the discount is at 0%—basically when there isn't even a discount—it doesn't try to divide at all. Just mess around with the IF function... tell it that if that field says 0%, it should just pull the value from above, but if it's anything else besides 0%, then go ahead and run the math.

Dana White4 said:When I try to enter a 0% discount, the whole thing just throws an error... I mean, I get why it’s failing—you can't divide by zero, obviously—but I haven't quite figured out which formula would actually prevent that from happening...

Wait, where on earth are you getting division from to calculate a discount? 😕

Shouldn't the discount just be the product of those two cells? I guess your current formula only "works" by pure coincidence when you set the discount to 10%.

Look, if you just take the total before any discounts and multiply it by the discount rate, you'll get the exact answer you're looking for... I guess it’s really that simple.

There are a few different ways you can tackle this, but these are probably your best bets:

1. Just use the IF function like Jose Miller3 suggested; try something like =IF(E2=0;0;E2*D3)
2. Or, you could just wrap it in an error handler, like: =IF(ISERROR(E2*D3);" ";E2*D3) (this keeps those annoying error messages from popping up). In this case, D3 would be your discount and E2 is the total.
3. If you really don't want anyone seeing those errors when you print, there's a little trick in the Page Setup settings. Just go to Sheet and set "cell error as" to . That way, the #DIV/0! won't show up on the hard copy, even though it's still sitting there in the software itself.

Benjamin Wilson7 said:There are actually a few different ways you could handle this—though I suppose the most straightforward ones would be:

1. just use an IF function, similar to what Jose Miller3 suggested; =IF(E2=0,0,E2*D3)

But then again, if E2 equals 0, then E2*D3 is going to result in 0 regardless... so, honestly, you probably don't even need the IF statement at all.

The absolute state of our education system really shows through here...😢

It’s honestly no wonder this country is drowning in debt... I mean, it actually scares me just thinking about how people are out there trying to calculate compound interest on their loans without knowing the basics...👎

So, here’s the basic formula...

P = (S * p) / 100

And for heaven's sake, you can't divide by zero!!!

velvetmason35 said:P =(S * p) / 100

Division by zero is a physical impossibility!!!

Oh, come on—you’ve got two zeros right there in the 100. 😁

Nicholas King said:No way—you've got two zeros right there in 100. 😁

Zeroes aren't here, but we've got ones. 😉

Nicholas King said:If E2 equals zero, then E2*D3 is also going to be zero. You don't actually need an IF statement for that.

Honestly, I was actually trying to put a blank string "" instead of a zero in the formula, but it slipped my mind—I wanted to hide the zero from appearing (which, by the way, you can just fix in the settings anyway).
Fair point, though!

help meeeeeeeeee 😕 😕

I need an Excel formula to round numbers, but I’ve got a pretty specific logic in mind.
The goal is to round to the first non-zero digit, like this:
567 > 600
501 > 500
0.845 > 0.8
0.883 > 0.9
But here’s the catch: if that first significant digit is a 1 or a 2, it needs to round up to the next increment. For example:
123 > 120
254 > 250
149 > 150
196 > 200
0.2938 > 0.30
0.01024 > 0.010
0.0206 > 0.021

Any ideas?

Douglas Lewis84 said:Any thoughts?

Is it possible for the numbers to be negative?

If no—well, actually, if they *can* be—here is what I’ve put together (assuming the number you want to round is sitting in cell A1):

=IF(INT(A1/(POWER(10;INT(LOG(ABS(A1))))))<3;ROUND(A1;-INT(LOG(A1)-1));ROUND(A1;-INT(LOG(A1))))
😁

Man, thanks a ton!! 🙏🙏

The numbers are always positive.

(What’s a log actually calculating? I get that along with integers it helps find the number of digits, but like, if log(283) = 2.451... what does that 2.451... actually represent?)

Anyway, thanks so much 🙂

Got another Excel question for you guys.
I’m trying to build a table where the columns actually function as lines.
Messing with the "gap width" isn't doing anything—they still look way too chunky. I need them to be actual thin lines, or maybe just super skinny rectangles.
Anyone got a fix?

Douglas Lewis84 said:I think I need to build a table where the columns actually function as lines.

Any ideas on how to pull that off?

Could someone clarify what he means here? 🙂

Excel columns need to be super thin, almost like they're just lines

I mean, why overcomplicate things? You can just set the column width to whatever you want. If you need it to be a tiny little line, fine—make it a line! What’s the big deal? Just go ahead and change the color or add a border if that's what the look calls for. (Just select the column -> column width -> maybe try 0.15)

My bad, I don't think I explained that clearly enough earlier. 😬
I’m talking about the actual bars in a bar chart, not just some random table in a spreadsheet.

Like I mentioned before: go to Format Data Series > Options > increase the "gap width"
But honestly? That DOESN'T work. They still look way too chunky—they need to be thin lines or super skinny rectangles instead.

Get what I mean now? 😬