Help with Excel spreadsheet please
Thread Starter
Join Date: Nov 2004
Location: Buckinghamshire
Posts: 84
Likes: 0
Received 0 Likes
on
0 Posts
Help with Excel spreadsheet please
Hi all.
I'm in the process of putting together a spreadsheet for invoicing purposes, I have most of it place but I'm stuck on one bit. I need to know a function that will display an answer if for example:-
The amount in cell A8 is less than £1 then multiply it by 100%
The amount is between £1 and £5 then multiply it by 50%
The amount is between £5 and £50 then multiply it by 30%
The amount is between £50 and £99 then multiply it by 20%
Hoping there is an Excel expert reading this who can help me.
Thanks in advance for any advice
Bigwings
I'm in the process of putting together a spreadsheet for invoicing purposes, I have most of it place but I'm stuck on one bit. I need to know a function that will display an answer if for example:-
The amount in cell A8 is less than £1 then multiply it by 100%
The amount is between £1 and £5 then multiply it by 50%
The amount is between £5 and £50 then multiply it by 30%
The amount is between £50 and £99 then multiply it by 20%
Hoping there is an Excel expert reading this who can help me.
Thanks in advance for any advice
Bigwings
Join Date: Oct 2002
Location: Guernsey
Posts: 12
Likes: 0
Received 0 Likes
on
0 Posts
The command you need is IF.
The basic format is =IF(AB=5,X,Y)
Translated this means IF Cell AB = 5 then Cell that this formula is in will be value X else it will be value Y.
To do want you want all you do is nest IF statements
so you would put in a cell where you want the answer
=IF(AB<1,AB*2,IF(AB<5,AB*1.5,IF(AB<50,AB*1.3,AB*1.2)))
Just be careful with the Brackets, as it's dead easy to get these mixed up, and always work in value order.
The basic format is =IF(AB=5,X,Y)
Translated this means IF Cell AB = 5 then Cell that this formula is in will be value X else it will be value Y.
To do want you want all you do is nest IF statements
so you would put in a cell where you want the answer
=IF(AB<1,AB*2,IF(AB<5,AB*1.5,IF(AB<50,AB*1.3,AB*1.2)))
Just be careful with the Brackets, as it's dead easy to get these mixed up, and always work in value order.
Join Date: May 2003
Location: UK
Posts: 154
Likes: 0
Received 0 Likes
on
0 Posts
You could use a nested 'if' statement. This one checks a value but doesn't multiply it. You'll get the idea though. I havn't got time to write the one you need right now, but if you PM me, I'll do it tonight. This should get you started though.
=IF(D13>201,13,IF(D13>101,15,IF(D13>51,17.5,IF(D13>1,21," "))))
Bests
Giz
Edit...OOOppss...Overcast types quicker than Gizmo
=IF(D13>201,13,IF(D13>101,15,IF(D13>51,17.5,IF(D13>1,21," "))))
Bests
Giz
Edit...OOOppss...Overcast types quicker than Gizmo
If you are willing to dive into a bit of Visual basic try this
From Excel press Alt F11 to open the VBA editor
Rt click on your project and insert => Module
Paste this bit of code
Function markup(cost)
Select Case cost
Case Is < 1
markup = cost * 2
Case 1 To 5
markup = cost * 1.5
Case 5 To 50
markup = cost * 1.3
Case 50 To 99
markup = cost * 1.2
Case Else
markup = cost
End Select
End Function
Then all you need to type in the display field is =markup(xx) where xx is the cell you want the calc to work on.
I have assumed if >100 quid then no mark up (the Case else line)
Steep learning curve but VBA can make excel sing!
From Excel press Alt F11 to open the VBA editor
Rt click on your project and insert => Module
Paste this bit of code
Function markup(cost)
Select Case cost
Case Is < 1
markup = cost * 2
Case 1 To 5
markup = cost * 1.5
Case 5 To 50
markup = cost * 1.3
Case 50 To 99
markup = cost * 1.2
Case Else
markup = cost
End Select
End Function
Then all you need to type in the display field is =markup(xx) where xx is the cell you want the calc to work on.
I have assumed if >100 quid then no mark up (the Case else line)
Steep learning curve but VBA can make excel sing!
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
BW,
The solution I would use is to create a table somewhere out of the way:
In this example I have used I3:I7 and J3:J7
In I3 enter 0
In I4 enter 1
In I5 enter 5
In I6 enter 50
In I7 enter 100
In J3 enter 2
In J4 enter 1.5
In J5 enter 1.3
In J6 enter 1.2
In J7 enter 1
Then in your target cell enter the formula:
=A8*INDEX(J3:J7,MATCH(A8,I3:I7,1),1)
EG
The solution I would use is to create a table somewhere out of the way:
In this example I have used I3:I7 and J3:J7
In I3 enter 0
In I4 enter 1
In I5 enter 5
In I6 enter 50
In I7 enter 100
In J3 enter 2
In J4 enter 1.5
In J5 enter 1.3
In J6 enter 1.2
In J7 enter 1
Then in your target cell enter the formula:
=A8*INDEX(J3:J7,MATCH(A8,I3:I7,1),1)
EG
Per Ardua ad Astraeus
Join Date: Mar 2000
Location: UK
Posts: 18,579
Likes: 0
Received 0 Likes
on
0 Posts
A query for bigwings/exgrunt - if I read the requirements ?as written?, should I3-I7 not be
0
1
5
50
99.01
and J3-J7 not be
1
.5
.3
.2
???
(as we do not know what happens over £99)
It all depends, of course, on what is meant by 'multiply' - the old conundrum!
0
1
5
50
99.01
and J3-J7 not be
1
.5
.3
.2
???
(as we do not know what happens over £99)
It all depends, of course, on what is meant by 'multiply' - the old conundrum!
Join Date: Sep 2000
Location: France
Posts: 69
Likes: 0
Received 0 Likes
on
0 Posts
Thread Starter
Join Date: Nov 2004
Location: Buckinghamshire
Posts: 84
Likes: 0
Received 0 Likes
on
0 Posts
There's a little-known button in Excel labelled Help
Thankfully some answered.
Bigwings
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
@BOAC
As written you are probably correct that I7 should read 99 if you want 98.99 to be multiplied by 1.2 but 99.00 not to be multiplied. In using 100 I did make the heroic and possibly erroneous assumption that 99 included values up to 99.99 (and just for completeness because I assume we are working with money I am only quoting to 2 decimal places - the formula will actually work to the 15 decimal places to which excel calculates).
On your second query I read Bigwings original request 'multiply the amount by... as show the total rather than show the increase which is what using the decimals on their own would produce.
@Bigwing
Just a further thought that if you are going to copy my formula down a column then you need to use absolute references to the table ie:
=A8*INDEX($J$3:$J$7,MATCH(A8,$I$3:$I$7,1),1)
All the best
EG
As written you are probably correct that I7 should read 99 if you want 98.99 to be multiplied by 1.2 but 99.00 not to be multiplied. In using 100 I did make the heroic and possibly erroneous assumption that 99 included values up to 99.99 (and just for completeness because I assume we are working with money I am only quoting to 2 decimal places - the formula will actually work to the 15 decimal places to which excel calculates).
On your second query I read Bigwings original request 'multiply the amount by... as show the total rather than show the increase which is what using the decimals on their own would produce.
@Bigwing
Just a further thought that if you are going to copy my formula down a column then you need to use absolute references to the table ie:
=A8*INDEX($J$3:$J$7,MATCH(A8,$I$3:$I$7,1),1)
All the best
EG
