Hi,
I am trying to get a spreadsheet working for work related issues, but I'm not really up to the more complicated formulas. I've tried looking around, but can't find the info out at all, so need some help.
What I am trying to do is this (if it makes sense):
If A1 is a negative figure, then divide it by 800. If A1 is a positive, then zero it out.
Hopefully, I'll be able to work everything else out (fingers crossed)
Thanks for any help you can provide
Lee
G'day Lee and welcome to the forum
Try this in B1
=IF(A1<0,A1/800,0)
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing theAdd Reputation button in my post.
Please don't forget to do the same to other contributors of this forum.
ThanksI don't void confusion, I create it
You didn't say what you want done if A1 = 0.
If that's a possibility you'll need to add another IF, otherwise:
=IF(A1<0,A1/800,0)
Thanks for the info guys.
That is working great, but one last thing.
The formula above that you gave me works great, but if there is a minus which it will divide by 800, it will show that calculation in a minus (-16000/800 = -20).
How do I get that figure showing a positive figure either in the same cell, or in another cell.
Last edited by freemonk; 03-27-2010 at 09:00 AM.
Try this
=IF(A1<0,ABS(A1/800),0)
Cheers
RC
Or this:
=MIN(A1,0)/800
and if you need positive result:
=-MIN(A1,0)/800
Never use Merged Cells in Excel
SUPERB!! That is working great.
Just one more issue popped up if you wouldn't mind helping me out again.
I'm using the =IF(A1<0,ABS(A1/800),0) that was provided about, but the issue I'm having now is that if I need 801, it needs to increase by 1. At the moment it's not.
So if I need 2400 it will show 3 (2400/800), but if it's 2401 it will still show 3, but it needs to show 4. How would I get around that one?
=ceiling(2401/800,1)
so
=IF(A1<0,CEILING(ABS(A1/800),1),0)
Last edited by martindwilson; 03-27-2010 at 10:55 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Working off of ZBor's which I like the best so far (it's IFless), then:
=CEILING(ABS(MIN(A1,0)) / 800, 1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Ok, thanks to everyone who has helped me out in this topic, it is much appriciated but...I have everything working apart from one last thing (I mean it this time! lol)
How would I go about joining a =A1-B1 formula to a =SUMIF("<0") formula.
I've tried =SUMIF(A1-B1"<0"), but it just error's. So if A1-B1 is a negative number, then I need it to show a zero.
Last edited by freemonk; 03-27-2010 at 12:40 PM.
Try again IF'less (like JBeaucaire said)
=MAX(A1-B1, 0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks