IF Command? Help with sales spreadsheet Please

[salessteve]

Link to Sheet

Hi all, thank you in advance for your help.

I have a recruitment business and our consultants use a sales sheet to track sales.

The first tab is an overview of the sales for all consultants.

The respective tabs after are for each consultant to record sales which of course pull through to the front page.

On the individual sales sheets - say Wiktoria for example (they are all the same)

It asks in cell K7 which type of sale it is PAYE or Umbrella.

If it is Umbrella then the cal is correct, Pay - Charge = Margin.

When it is PAYE there are additional costs to pay and so the Pay - Charge doesnet = the true margin as there are oncosts which we have to pay the government.
These costs total about 18.1%

So the true margin if PAYE is selected should be pay + 18.1% - charge = margin.

I need the calc to change if PAYE is selected, i feel like this should be an IF command in the background but I am not skilled enough to work it out or how to apply it.

Can anyone help?

 

[etaf]

18.1% of what - which cells

=IF( K7 ="Umbrella", Pay - Charge, (pay * 1.181%) - charge)
assuming only 2 choices in K7
otherwise

=IF( K7 ="Umbrella", Pay - Charge, IF( K7="PAYE", (pay * 1.181%) - charge, "" ))

 

[salessteve]

sorry i havent explained my self well.

I need the Margin cell - Row N to be accurate.
It needs to recognise the choice in Row K as Umbrella or PAYE.

Then make the correct calculation
Umbrella is just Pay - Charge = Margin and this is correct.
PAYE is pay + oncosts (18.1% of the pay) - charge = the real margin.

 

[etaf]

OK
ROW k , i asusme you mean column

So in column N

what row - as you mention K7 , is that a fixed cell or does the data just start from row 7

then in N7

=IF( K7 ="Umbrella", Pay - Charge, IF( K7="PAYE", (pay * 1.181%) - charge, "" ))

SO if K7 contains the word Umbrella - the calculation in N7 - would be Pay - Charge - but i have no idea what cell you are using for PAY or for charge
and if its PAYE - then it will calculate the margin - PAY*1.181 is the same as (PAY + 18.1% of PAY)
(pay * 1.181%) - charge

again no idea what cells have the PAY or the CHARGE

Otherwise - sorry i dont know why , the formula is not whats wanted

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[salessteve]

Understood, there is no sensative data in the sheet. But let me see if i can replicate it on a smaller one and then it might be easier to show.

 

[salessteve]

Line	Booking Type	Client Full Name	Candidate Full Name	Days	On Quba?	Job title	Which days worked?	Finish Date	Umbrella - PAYE	Pay	Charge		Total Pay	Total Charge	GM for week	Notes
W/E Friday 8-11	Margin
1	D2D	St Stephen School	David Jones	5	Yes		M/T/W/T/F		Umbrella	118.5	150	31.5	592.5	750	157.5
2										0	0	0	0	0	0
3										0	0	0	0	0	0

 

[etaf]

thanks
you sample does not have PAYE in and expected result

so LINE 3 on the image
you have 31.5 - thats a MINUS - 31.5 as the charge is more than the PAY
anyway

I used
=IF( K4 ="Umbrella", L4 - M4, IF( K7="PAYE", (L4 * 1.181%) - M4, "" ))
not sure if that is whats needed

See column S - titled ETAF with the fomula

i left A blank to show the same columns as K and N

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Line	Booking Type	Client Full Name	Candidate Full Name	Days	On Quba?	Job title	Which days worked?	Finish Date	Umbrella - PAYE	Pay	Charge		Total Pay	Total Charge	GM for week	Notes	ETAF
3		W/E Friday 8-11	Margin
4		1	D2D	St Stephen School	David Jones	5	Yes		M/T/W/T/F		Umbrella	118.5	150	31.5	592.5	750	157.5		-31.5
5		2										0	0	0	0	0	0
6		3										0	0	0	0	0	0
7
8
9
10
11
12
13
14											IF( K7 ="Umbrella", Pay - Charge, IF( K7="PAYE", (Pay * 1.181%) - Charge, "" ))
Sheet1

Cell Formulas
Range	Formula
S4:S6	S4	=IF( K4 ="Umbrella", L4 - M4, IF( K7="PAYE", (L4 * 1.181%) - M4, "" ))