Help with Excel spreadsheet please

[Bigwings]

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 :ok:

[overcast]

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.

[gizmocat]

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 :ok:

[Bigwings]

Thank for the quick replies guys, I'll give them a try.

Regards

Bigwings :)

[Agent86]

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!

[ExGrunt]

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

[Bigwings]

Agent86 and ExGrunt

Thanks for the suggestions, enough here to keep me happy for a few days.

Thanks again all

Bigwings :)

[BOAC]

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!

[matelot]

... I need to know a function ...
There's a little-known button in Excel labelled Help. :p

Search on function, maths functions, IF statements et al., and you'll get a plethora of examples. Very useful if trying to construct an auto-calculating plog as well.

[Bigwings]

There's a little-known button in Excel labelled Help

Tried that before posting my question matelot but as I wasn't to sure of what I was looking for I thought I'd ask more knowledgeable people than me.

Thankfully some answered.

Bigwings :rolleyes:

[ExGrunt]

@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

[BOAC]

Absolutely! It is the old 'multiply by' question. Just thought I'd send bigwings a 'thought bubble'.:)