Free Microsoft Excel 2013 Quick Reference

Turnover Formula

I am trying to do a CountIf formula to calculate the amount of turnover in 30, 60, and 90 days.

I currently have a roster with a Higher Date and their termination date.

any suggestions ?


Hi,

I have a table of customers, the number of times they've complained and the turnover they've done.

what is the formula to work out the number of complaints for ever £ spent ?

Hello, I need help with a formula that I've been wracking my brain on. Let's see if I can explain what I need.

Basically I need the formula to perform a vlookup to pull in the YTD Termination count from a pivot table. If the value exists, then perform a Turnover Formula using that value. If the value does not exist, then return 0.00%.

I hope I was able to articulate what I need, I'm finding it difficult to explain.

Any suggestions?

Please help!! I need to know if anyone knows of any formula to calculate employee turnover ratio and if there is a place to get a free template to download?

This is my first time so please don't hurt me: D
I have four columns:
In the first column there are just figures relating to a firm's turnover.
I want a formula that works out three different things.

So here is hoping that I explain myself correctly.

if the amount is 0 to $150,000 it should return a result of $600
if the amount is $150,001 to $1,000,000 is should be multiplied by 0.4%.
if the amount is over $1,000,000 is should be multiplied by 0.3%.

Example $130,000 should return $600

I have worked them out individually but do not know how to combine them.

Spreadsheet attached from a grateful girl.

I use a simple spreadsheet to track my sales people. It displays their sales figures as follows.

Monthly turnover target in Rows A4:L4
Actual sales figures in Rows B4:L4
Monthly % versus target in Rows C4:L4
Annual YTD % in Rows D4:L4
The columns are headed by month January to December.

In M4:M7 is the summation of the relevant row. However, I need a simple formula that will display the YTD % in Cell M7. Because there are no sales for the months to come in the year then it calculates incorrectly. I know that the correct figure is shown in the relevant cell of Row D4:L4 but my boss needs to see it in the summation column.

Is there a formula that will calculate the % YTD and recognize that there are blank months and that we are only part way through the sales year? For example a seller that has sold £150,000 against an annual target of £200,000 is 75% of his target and someone who has sold £210,000 is 105% with several months still to come.

I have attached a small sample of the sheet.

All help gratefully received

Thanks in advance

Dave

Hi all!!!!

I may have had one to many soft drinks last night but I can't work out this formula...
I am working on a report that displays staff turnover for the year, I woud like for the average turnover percentage to be zero for month if the average daily supply is 0.
I have attached a copy of the report im working on.

Can anyone suggest anything?

Good afternoon,

I am trying to create a turnover report in which I deduct workers from one company and add them to the next one (see attachment).
For example: If my worker moves in a specific month (eg. April, the month is determined by 'Sheet1' cell F1) from company 1 to company 2, then I want on 'Sheet1' the value -1 in cell A4 and a +1 in cell B4, etc. So I need a formula that looks for all workers in April who moved from one company to the next one and deduct those numbers from the particular cell in 'sheet1' (A4, B4, C4 or D4) and add it to the new company's cell in 'sheet1'.
I hope this makes sense and that someone can help me. I tried the COUNTIF function, but that doesn't work with the month variable in it. And I couldn't get the SUMPRODUCT to work either.

Thank you so much for your help!!

I'm generally pretty good with these things, but this one seems to be beyond my skill level.

I have a list of employees, which has hire dates and termination dates. These are named HireDates and TermDates. I am calculating some turnover stats for our HR department. Here are some formulas I have so far:

Turnover Stats

CDE2From Date1/1/2007 3To Date12/31/2007 4 5Hired348Net Hires6Terminated259897Avg LOE 8 9Active (begin)594Net Gain10Active (end)68389
Spreadsheet FormulasCellFormulaD5=SUMPRODUCT(--(HireDates>='Turnover Stats'!$D$2),--(HireDates=$D$2),--(TermDates

Hi,

I am trying to pull data from a number of sheets to create a spreadsheet db were the sheet name is a company. e.g.

Column A Column B
Company Turnover
Company A £10m
Company B £4m
Company C £3m

I have the turnover in individual sheets with informaition about the company and the sheets are named as per the company.

Can I use a formula to extract this information using the data from Column A above?

I assume most, if not all, of us here have encountered a certain key perfomance indicator called employee turnover (or staff turnover). I have seen it a lot and didn't haven't really given much of a thought until it somehow ended up in one of the excel files I am administering. And now I must devise the formula to compute for it.

First of all, what is the correct way of computing it? Is it just flat out the # of employees who resigned/fired divided by average # of employees? If this is so, then it is very possible to have more 100% turnover.

If there are 3 positions in a company, A, B, C and position A has been replaced 3 times in a year, what is the turnover? And will it be different if all 3 position has been replaced but just once?

What if the company is in a liquidation phase, say at the start of the year 2006 it has 300 staffs, then by end of June 2006 it had none (50 staffs resigned per month). What is the annual staff turnover by end of 2006? And what is the YTD staff turnover in each of the months?

I have asked several people in the company I worked in and they have differing opinions.

Would like to hear from you guys.

Thanks

Hello

I`ve got a query with the sum of Turnovers each month ( fieldnames: Year, Month, SumOfTotalPrice) I need to calculate the commissions for each month. The commission agreements are like this: 5 % first 10 Millions
3 % next 10 Millions
2 % above 20 Millions
I wrote a Function to calculate the commissions like this:

Public Function CommissionP(SumOfTotalPrice As Double) As Double
If SumOfTotalPrice 10000000 And SumOfTotalPrice 200000000 Then
CommissionP = ((SumOfTotalPrice - 200000000) * 0.02) + (10000000 * 0.03) + (10000000 * 0.05)
End If

End Function

I know that might not be the best way but it works to calculate the commission for the whole year. My problem is, that the commission agreement is for the whole year, and I have to calculate the commissions for each month. The formula doesn`t work in this case. As I am not that experienced in vba programming I would like to know how I to write a formula like this. I need to somehow sum the cells of the first months of the year and add this summ to my formula. I hope someone can help me out...regards steven

Hello,

This forum looks like a great resource, and if it isn't too much trouble for it's members to take a look at my problem I would be much indebted for their help. It's not really a problem, just a time saving technique that I'm not sure how to employ.

Attached is a workbook I have been working on. I needed to calculate some formulas for Turnover values based on Monthly Sales and Average Inventory Quantities. If you look on Sheet 1 (Monthly Sales) you'll see I've completed the first 6 entries, and everything after that (starting with Item 01-000008) is still in the format of how I received the information. Basically it only has the Monthly Sales (Price and Quantity Sold in Column C).

So as you can probably tell, my dilemma is how to most efficiently fill in the formulas and rows for the rest of the ~800 items. I simply filled in the first 6 entries manually by inserting rows and copying the formulas, but completing 800 more would require more time than I would like to spend on this task. If some experienced Excel user has a technique to share, I would very much appreciate the effort.

Thanks in advance.

- Mark

I'm having a lot of trouble getting this formula to work:

I wish I could make this post shorter, but I'm not sure what details will be critical to getting an answer, so I've included
all details.  

The way it works now is I have a master workbook named “2011 EP” (2011 Engagement Plan). This workbook takes my input on a monthly basis, combines it with metrics provided by my home office (each monthly metric is named 01.xlsx, 02.xlsx…), and shows a snapshot of our effectiveness compared to our activity. I am a trainer, and the workbook shows my effectiveness as a trainer for my individual office’s employees.

At the end of every month we are expected to update our employee list and our activity with each of them. Then the workbook looks up the contact from my sheet in the monthly metric report and returns a value for each category we train in. The monthly metric report contains the data for every employee in our company (around 12,000 people in total), yet my individual office has 120-150 employees depending on the month (each office in my organization is designated by a number).

As it is right now, they have us manually updating our employee list and we have a high turnover, so this can be a burdensome process. We can’t just delete or copy/paste people in because there are formulas built into the worksheet that end up not functioning when you do that, and they will reprimand me if I have any errors in my report.

What I want to do is be able to select the office number I'm running the report for and then have the sheet autofill each employee. I have been able to make this work by copying the text from the workbook provided by home office into my workbook and then calculating from there, but after simulating this over a 12 month period my workbook becomes too large to send to home office.

I know the index function could get the job done, but I am having trouble with syntax. There are a couple factors that I think the error may be arising from. The first is our office numbers are designated by 1-128. However, in the monthly metric report the numbers are provided as 5 digit numbers that make use of leading zeros (ie-office #48 looks like “00048”). In other formulas I’ve had to use the text function to avoid errors with this and the formula ends up looking like text(b2,”00000”).

The other issue I’m coming up against seems to have to do with referencing cells in a separate workbook. So, here’s the formula I’ve written. Below it is another variant that I’ve tried without success. Does anyone see the issue that’s causing this to not work? I’d love to share the workbook and monthly metrics, but it’s confidential information. The formula is to go in EP 2011. I have it looking up info from a four column section from 01.xlsx. Column 1 contains the office number, column 4 contains the employee name, the range lookup is A2:D12500. Also, B2 contains the office number.

	

	
Should I use a different formula altogether?  Any guidance will be greatly appreciated

Hi All,

I have a formula which goes as "=VLOOKUP(597, SheetX!$A$1:$C$10, 3, 0)"

where SheetX is the name of the sheet the formula reads from, and this sheet name will keep on changing. So the sheet names are in a column (NAV, Profit, Turnover and so on)

A1 NAV
A2 PROFIT
A3 TURNOVER

Now as I drag the formula down it should change as below taking the sheet name from Column A and substituting it in the formula:

B1 =VLOOKUP(597, NAV!$A$1:$C$10, 3, 0)
B2 =VLOOKUP(597, PROFIT!$A$1:$C$10, 3, 0)
B2 =VLOOKUP(597, TURNOVER!$A$1:$C$10, 3, 0)

Any ideas how this can be achieved? Thanks

Hi all,

I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)

The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents
.
e.g. Paul is the description in am looking for in "Paul is riding his bike"
If so, then add the turnover to Paul

I have made a demo to clarify what I mean.

What to do?

Thanks in advance
Hein

I need to find a way to calculate Annualized Turnover as a % for my clients. Attached is a view of the report I am working on. I have a column for new additions, new terminations, net additions and turnover %. I need to find a formula that works, the one I am using doesn't seem to be correct, and I also need to display zero if that is the answer.

Formula for Turnover % - {=(H5*12)/ABS(J5)}

I tried to add an If formula, but it changed the outcome of the %.

I also need to calculate a running average per month total for each client and am not sure if there is a formula that will average each month on its own without me having to change the divisor each month...

any help would be great...

Hi,

I am trying to create a sheet which had a lot of info on one sheet and then a neatly presented second sheet which calculates values (such as turnover per square metre). On the second sheet I have formulas such as =SUM(BaseData!I3/BaseData!E3) The cells which are referred to in this formula have no data in which I have put as N/A on the first sheet, therefore in the final sheet I am confronted with a #VALUE! error.

I want this to display some sort of message such as 'N/A' or 'Not Available' rather than the error and (more importantly) I need these to be ignored from any kind of further formulas such as the ranking of these values or the averaging. Having searched on Google and this forum, it appears that IFSERROR OR IFERROR could be a solution, but I'm not sure of the exact formula or how to combine it with the existing formula.....

Apologies if the wording of this post isn't very clear, I can post a picture if it would be easier to explain? I'm very new to the more advanced side of Excel and so any help would be greatly appreciated!

Thanks in advance for any help that you can offer!
Ross

Hi, hair pulling time. I have an excel array formula which references a number of ranges on a different worksheet (within the same workbook - see formula below). It works fine for explict cell ranges (e.g. 'MyDataSheet'!K13:K300) but will not work for dynamic named ranges defined by an offset formula (=OFFSET('MyDataSheet'!$K$13,0,0,COUNTA('MyDataSheet'!$K13:$K300),1)).

Does anyone know if there is an issue with using dynamic named ranges in array formulae on different worksheets within the same workbook?

{=SUM(IF('PGLSE Turnover'!K13:K300=$B5,IF(RIGHT('PGLSE Turnover'!G13:G300,2)=F$2,'PGLSE Turnover'!I13:I300,0),0))*$C$5} (array formula entered with ctrl-shft-enter)

Hi can someone help please?

Im am trying to create a formula that will calulate what sales need to be added per month to achieve a particular total sales total at year end based on the following paramters for example;

i have the 1st month sales figure of say 10,000,
i have the total target figure at year end of say 3,000,000 total sales for the year.
The 10,000 signed up sales in month 1 will continue in month 2, 3 4 and so on.
so for example if 11,000 was signed up in month 2 then the Turnover for month 2 would be 21,000 and that 21,000 will continue through to months 3,4 and so on. Again if 14,000 was signed up in month 3 then the turnover in month 3 will be 10,000 + 11,000 (21,000) +14,000 so 35,000.
I would assume linear growth in the formula.

i attach an excel file that documents lightly what i've outlined above

im near 40 and class myself as relatively excel savvy - this is my first post however on a forum of anytype before so perhaps there's a discussion on that some other time.

thanks

I apologize in advance for the typo's.

Here's an example of what i have and what i am trying to do. For reference, i have attached the file with 2 sheets referencing each other in the same workbook and a file with each source and display files.

I don't expect anyone to do any the work, i just need to know how to edit the following formula to make it see the exact cell but just in another workbook.

I have the following formula in place to reference from one sheet to another in the same workbook:

=IF('Inside Turnover'!$E$45<>"",'Inside Turnover'!$D$45&" "&'Inside Turnover'!$E$45,IF('Inside Turnover'!$D$45="","",'Inside Turnover'!$D$45))

BUT i want to do this using IST1 as the source where someone can do input and update while IST2 is displaying the changes on a monitor in another room.

The problem i am having is this.

The above formula is doing the following:
An entry is made in D45 on the Inside turnover sheet. Another entry is made if needed into E45 that turns the cell fill to yellow in D45. If an entry is made in E45, it turns the text in D45 Blue. E45 and D45 are never used at the same time. I also have conditioning formulas making the color changes.
Attachment 145242Attachment 145243Attachment 145244

If you look at the inside turnover NEW. that's what i want to do but in 2 different files as described above but i am having trouble gettingthe different files to act the same as the original file does.

When opening the files. i only want the columns D. E. F. to have the formulas to have an entry on Inside Turnover tab of the Inside Turnover(NEW) file which i have also saved as Inside Turnover1. I'm sure it will be obvious. Yeah right. haha
TMI? or not enough?

Hi
I have a data group with ten groups and each group has a corresponding value. Actually this is a rating table with ranges like $0 to $10000, $10001 to $25000, ....., over $2500000. each set has a price rate.
Since these are more than 7 values, IF formula is not going to work.
i though of using Vlookup but do not know how to write these price ranges to create a table.
Can someone sort it out for me?
Kinds urgent.

thx
khan

I have a worksheet in which lower rows contain a lot of table and workings for the formulas used in top rows.
It looks rough and I want that the user can not go or see the lower rows after row82 and neither go beyond column L.
So the scroll area should be A1:L82.

Bearing in mind that this hidden area has a lot of vital info on which the working area calculations depend.
I tried couple of VB codes from this forum but nothing seems to work for me.

someone help!

Hi Excel forummers, would like to seek your advice on how to return the top 5 results in a set of data.

The file can be found here: https://docs.google.com/open?id=0BzQ...1JKSEFGdHJlX00

Extra info are in cells A1, AN1, AN9, BC9

In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.

I think rank, concatenate and possibly some other formulas might be required. If you have any idea, please suggest and I will be grateful. I am also open to VBA formulas

Jason

Added
Posted on http://www.mrexcel.com/forum/showthr...=1#post3209858,
http://www.excelforum.com/excel-gene...-formulas.html and
http://chandoo.org/forums/topic/use-...x-match-or-vba

Hi everyone! I'm new to the forum, and if I posted in the wrong section, please let me know.
I have the following problem in Excel 2007:

I need to make a table in which i have a dropdown list. For every value that I pick (let's say X), a cell with the formula :
=VlookUp(Data_In_a_Cell;ArrayX;2;False)

So for value 1 of the pick, the cell will modify its formula to : =VlookUp(Data_In_a_Cell;Array1;2;False) ;
for value 2, the formula will be: =VlookUp(Data_In_a_Cell;Array2;2;False) and so on.
Assuming that i define Array1, Array2,.... ArrayX , how do I do that?

I tried using VBA, but it displays me an error, because the code wants to keep the proper value of the cell, but as soon as it replaces, Excel will do its job and input another value of the cell. The code won't work

Next I tried using Replace function, but I don't quite get it...
Please help me if you can.
For any questions, I'm available. See you!

Other forums I asked:
http://www.excelforum.com/excel-general/846992-i-need-to-change-arrays-in-a-formula-if-value-in-a-list-changes.html
http://www.ozgrid.com/forum/showthre...601#post619601