- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Question - Creating formula with percentage and minimum/maximum value
Posted on 4/6/15 at 8:17 am
Posted on 4/6/15 at 8:17 am
I've looked online but haven't had any success so I hope someone here can help me out. I am trying to create an excel document for tracking purposes but am getting hung up in one area (so far).
In my scenario, column A is the amount of sale. Column B is the commission on that sale, which should be 5%, but no lower than $35, and no greater than $150. Can anyone help with this? If you need more information please just let me know. Thank you
In my scenario, column A is the amount of sale. Column B is the commission on that sale, which should be 5%, but no lower than $35, and no greater than $150. Can anyone help with this? If you need more information please just let me know. Thank you
1
Posted on 4/6/15 at 8:27 am to josecanseco
Nested if statements would work. E.g.:
=IF((0.05*A1)<5,5,IF((0.05*A1)>150,150,0.05*A1))
=IF((0.05*A1)<5,5,IF((0.05*A1)>150,150,0.05*A1))
Posted on 4/6/15 at 8:37 am to BamaAtl
Thank you - but shouldn't there be a "35" in there somewhere?
Posted on 4/6/15 at 8:42 am to josecanseco
Yeah, he accidentally did 5,5 in the first if. Should be 35,35.
That's how I would do it too
That's how I would do it too
Posted on 4/6/15 at 8:48 am to josecanseco
Ok, one more follow up. With the new formula in, it is leaving it as "35" for all of the empty slots too (worksheet has set amount of lines but some will be unfilled if max sales numbers aren't hit - so right now a blank sheet it still shows them 35 commission for every line available). Is there a way to make it 35 only if values are entered into a previous column?
Posted on 4/6/15 at 8:48 am to aaronb023
quote:
Yeah, he accidentally did 5,5 in the first if. Should be 35,35.
Yep! Sorry about that typo.
Posted on 4/6/15 at 8:49 am to josecanseco
You can use the isblank function to denote whether a column is filled, or just add another if statement looking for whether the value in A is greater than 0.
Posted on 4/6/15 at 8:51 am to BamaAtl
Thank you for prompt response - any chance you can show me where to insert that into above formula?
Posted on 4/6/15 at 9:32 am to josecanseco
Try this:
=IF(A1<1,".",IF((0.05*A1)<35,35,IF((0.05*A1)>150,150,0.05*A1)))
You can swap out the "." for whatever you want a blank field to show. That's assuming you don't have any sales of less than 1.
=IF(A1<1,".",IF((0.05*A1)<35,35,IF((0.05*A1)>150,150,0.05*A1)))
You can swap out the "." for whatever you want a blank field to show. That's assuming you don't have any sales of less than 1.
Posted on 4/6/15 at 8:17 pm to BamaAtl
Don't need the period inside the quotes if he wants the cell to be blank when no sales have happened.
Posted on 4/6/15 at 10:02 pm to lynxcat
Thank you all very much, this worked just like I needed it to.
Posted on 4/6/15 at 10:40 pm to josecanseco
This is a bit cleaner in that each subexpression only occurs once:
=IF(ISNUMBER(A1),MAX(35,MIN(A1*0.05,150)),"")
Also, all the literals really ought to be named constants, e.g. MinComm, MaxComm, CommRate.
=IF(ISNUMBER(A1),MAX(35,MIN(A1*0.05,150)),"")
Also, all the literals really ought to be named constants, e.g. MinComm, MaxComm, CommRate.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News