solve this EXCEL formula for me before i go CRAZY please

Wallace · 22 Sep 2013 at 20:39

movingtables said:
so 23% would look like this:

B11*1.23

?

Yes, if you divide your desired percentage by 100 then you will get the decimal value to use.

so 5 % = *0.05,
50 % = *0.5,
150 % = *1.5,
500 % = *5.0.

movingtables · 22 Sep 2013 at 20:41

in normal maths how would one calculate 10% added for someones wages?

would it be for example.

£100 * 10% = 1000? i think not.

£100 + 10% = £110 yes!

yet in the excel formula we use * instead of +.

i just don't get it.

Wallace · 22 Sep 2013 at 20:42

And if you want to give someone a bonus you are giving their normal salary (100 %) and the desired bonus amount (23 %) so 123 % in total (*1.23 decimal).

Sorry if that sounds patronising, just trying to help.

Edit: Had typed this before the post above! Boom

Hope that makes sense.

100 * 10% = 10% of 100, so 10.

100 + 10 % (of 100) = 100 + 0.1*100 or = 1.1*100

So the formula =IF(B11>3000,B11+B11*0.1,"failed to make bonus") would also work...

RDM · 22 Sep 2013 at 20:44

Just to confuse further, good practice would be to have the bonus percentage in a different cell and reference that cell in your formula. That way you only have to change it in one place.

dirtychinchilla · 22 Sep 2013 at 20:45

RDM said:
Just to confuse further, good practice would be to have the bonus percentage in a different cell and reference that cell in your formula. That way you only have to change it in one place.

Careful, he might pop

DaleTheWhale · 22 Sep 2013 at 20:45

£100 * 1.1 = £110 (10% increase)
£100 * 5 = £500 (500% increase)
£100 * 10 = £1000 (1000% increase)

movingtables · 22 Sep 2013 at 20:47

thanks for your time guys i will study your answers.
will have to read it many times.

Wallace · 22 Sep 2013 at 20:47

RDM said:
Just to confuse further, good practice would be to have the bonus percentage in a different cell and reference that cell in your formula. That way you only have to change it in one place.

As well as the threshold.

So the perfect sheet would have the bonus threshold in say B12, and the percentage bonus in B13 (formatted as a percentage) then you could have.

=IF(B11>$B$12,B11+B11*$B$13,"failed to make bonus")

Then you can fill that across, the $ references lock it to the single cells B12 and B13 when you fill across or down.

RDM · 22 Sep 2013 at 20:49

Wallace said:
As well as the threshold.

So the perfect sheet would have the bonus threshold in say B12, and the percentage bonus in B13 (formatted as a percentage) then you could have.

=IF(B11>$B$12,B11+B11*$B$13,"failed to make bonus")

Then you can fill that across, the $ references lock it to the single cells B12 and B13 when you fill across.

Too true, should have mentioned that in the previous post. Though rather than using locked references named cells would make it easier to read.

dirtychinchilla · 22 Sep 2013 at 20:52

I think you're over-complicating things slightly, considering

Wallace · 22 Sep 2013 at 20:52

RDM said:
Too true, should have mentioned that in the previous post. Though rather than using locked references named cells would make it easier to read.

Explain? I might learn something from this thread also

{SAS}TB · 22 Sep 2013 at 20:53

Please tell me this is homework and not actually something for work

dirtychinchilla · 22 Sep 2013 at 20:54

Make a single cell into a named ranged (ctrl + f3) and then reference that cell in the formula. You can then move the cell around all you like, as much as you retain the cell name.

{SAS}TB said:
Please tell me this is homework and not actually something for work

Hah! My first thought. I'd worry for his business.

RDM · 22 Sep 2013 at 20:54

Wallace said:
Explain? I might learn something from this thread also

Using your above example name cell B12 Threshold and name cell B13 Bonus and then your formula would be:

=IF(B11>Threshold,B11+B11*Bonus,"failed to make bonus")

Wallace · 22 Sep 2013 at 20:56

dirtychinchilla said:
Make a single cell into a named ranged (ctrl + f3) and then reference that cell in the formula. You can then move the cell around all you like, as much as you retain the cell name.

Hah! My first thought. I'd worry for his business.

RDM said:
Using your above example name cell B12 Threshold and name cell B13 Bonus and then your formula would be:

=IF(B11>Threshold,B11+B11*Bonus,"failed to make bonus")

Definitely did learn something, I didn't know you could name cells! Thanks guys.

dirtychinchilla · 22 Sep 2013 at 20:57

I believe it's more commonly used for groups of cells, as opposed to single cells. That's in my experience though!

movingtables · 22 Sep 2013 at 20:57

OK GUYS I THINK I HAVE IT !!!

My new formula is based on 10% bonus on top of wages
of £3,000 or more.

I used an absolute cell reference for the 10% (so i can just change the value
if i want to and all the other cells should update with new %)

Is my cell formula correct?

RDM · 22 Sep 2013 at 20:59

dirtychinchilla said:
I believe it's more commonly used for groups of cells, as opposed to single cells. That's in my experience though!

Yeah, but the programmer in me likes my if statements tidy!

Also you don't need to use Ctrl+F3, you can just type the name in the little box in the top left that normally has the cell reference.

dirtychinchilla · 22 Sep 2013 at 20:59

You might want to make it greater than or equal to £3000, if this is the case?

If so you'd have B11=>3000 as opposed to B11>3000

RDM said:
Yeah, but the programmer in me likes my if statements tidy!

Also you don't need to use Ctrl+F3, you can just type the name in the little box in the top left that normally has the cell reference.

Haha fair enough. I'm no programmer. Yeah but the box in the corner doesn't allow you to modify the ranges once you've named them.

Wallace · 22 Sep 2013 at 21:00

movingtables said:
OK GUYS I THINK I HAVE IT !!!

My new formula is based on 10% bonus on top of wages
of £3,000 or more.

I used an absolute cell reference for the 10% (so i can just change the value
if i want to and all the other cells should update with new %)

img

Is my cell formula correct?

As stated it's correct yes! If this homework then move G2 to H2 and add the label bonus rate in G2 and it looks perfect. You can also change the format of the bonus row to currency to make it look neater.