Any excel gurus out there?

[I used to good.Honest]

Ok just trying to do a formula for an excel spreadsheet that seems simple enough but it won't do what I want.

=IF(C7<7,1,C7*0.15) that's the formula.
Basically I have bought into an auction house and when sellers bring in items we used to charge 15% commision, now we are putting a minimum commission of £1 on any item, so when I put 1,2,3,4,5,6 or 7 in C7 the figure will come out as 1 or anything over 7 then it's at the normal 15%, but the problem is if an item doesn't sell then we carry it over to the next auction but for the seller to get paid before the next auction we do their account, but if it doesn't sell then we put 0 (zero) into C7 but it keeps coming out as 1 . I've tried to add another parenthesis and IF(C7=0,0) but it doesn't like it and won't recognise it as a function.
It should be easy but I am starting to want to smash my computer now.
Anyone have any notion of how to read C7 as 0 and not put a 1 there?????

 

[Tom]

I put it in row 6 downwards, nest the formula with an if that leads into an if

If 0 then 0
Else if < 7 then 1
Else calculate percentage




=IF(C6=0,0,IF(C6<7,1,C6*0.15))

 

[I used to good.Honest]

ok, thanks, but there are usually lots of items on each sheet.
It's just when we print out the sheet for the customer then if it say 0 and then then column next to it says 1 then they will complain.

 

[I used to good.Honest]

actually that works
thanks dude

 

[Tom]

Yes, just the way I listed it out on seperate lines to show other answers


I would say though that it would be a 'better' method to not have .15 in the formula, especially if there could be multiple items and multiple copies of the formula

Eg best practice would be to layout a form with a box somewhere that shows the commission rate
Then in the formula link to the cell which contains the rate which means when / if you change rate then you just change the percentage instead of modifying the formula

When you have multiple items on the sheet are they one customers items, and if so is the commission worked per item or per customer sales?
Per item the formula is good as is, if all of the sales then it's going to keep adding the minimum £1 if prices are low

 

[I used to good.Honest]

yes only one customer per sheet.
commission is worked out per item.
But it works, so thanks Tom, you win the internet for the day