Search the Community

Link: Microsoft Excel can now put multiple values in a single cell - Digital Trends

Hi, Does anyone know how to make a pivot table count in Excel show names rather than numbers? I want it to create a matrix tracking students KS2-current data outside of SIMS. Thanks

Hi I have a spreadsheet that now shows a league table (top 10 students) and when I filter the main table, my league updates which is great... I have this type of thing: =INDEX($C$3:$C$1380,MATCH(AA1409,$Y$3:$Y$1380,0)) When I filter say Blue House - in the main table only Blue House students shows but in my league Student 4 comes through. I assume this is because there is no students in Blue House in position 4. The issue I have now though is e.g: Student 1 Blue House 0.5 Student 2 Blue House 0.5 Student 3 Blue House 0.4 Student 4 Red House 0 I hope I've explained that ok? any help appreciated Thanks

I've recently spent quite a while trying to get the following formula to work and i finally have, but now i need to figure out how to not only change the text appearing in the cell but also the colour of that cell. The formula is as follows: =IF(COUNTIFS('Group Info'!C17:'Group Info'!F17,"<>"&"",'Group Info'!C17:'Group Info'!F17,"<>"&"Yes"),"Not Ready","Ready") ..This formula basically means that if all 4 cells on another worksheet ALL say 'Yes', then this cell will show 'Ready'. But, if any one of those cells doesnt say 'Yes' then to reply 'Not Ready'. Now, can anyone help me with not only changing the text but also changing the colour of the cell to red if 'Not Ready' or green if 'Ready'? Thanks.

Hi Im trying to set up a series of what if calculators for up subjects. Basically, what I want to do is to: 1. Use 4 matrix p8 calculations for student 2. Work out how much each grade effects p8 score of students for subject and overall.school grades 3. Produce a calculator for each subject area, so staff can work towards a set p8 department figure 4. Automate this, so staff can just change the grades and everything else updates. So far I have; 1. Worked out p8 changes for each subject by increasing/decreasing grades 2. Worked out p8 changes for whole school by increasing/decreasing subject grades So my question is, what is the easiest way to set this up and what are the next steps? Is there a way to do this on 4matrix or do I need to build some custom spreadsheets. Thanks

Hey all, I hope this is the right place for this - if not move it and slap me! All,I have this formula (inherited from a manager who has left the school).=IF(N$3>$H$3,0,($L8*$E$3+IF($I8>0,-30,0)-O8)*IF(SUM($J8:$K8)>0,0,1)) Basically it is working out money owed to the school by students doing extra curricular music lessons. Some students are eligible for free lessons (if they have free school meals or if the lesson is part of their GCSE course etc) all the others have to pay a fixed amount per lesson. They get a discount if they are learning a set pair of instruments. The formula works for everyone who has to pay. If any student is in the FSM or GCSE group then as long as the amount owed is greater than £0.00 then the formula works. If the value owed is less than £0.00 (for example they've paid but have since moved to a FSM or GCSE group) then the amount owed always shows as 0,00 and not - - the sheet should display the credit amount as a red negative value. Example values for a student in the FSM group are :N$3 - this is a date $H$3- also a date $L8 - is the number of instruments being learned - this is at 1 for all students - I assume this means that if they are learning a set pair that doesn't count as multiple instruments because of the discount. $E$3 - the current charge for a single lesson $I8 - this is set to one if the student is entitled to a discount as they are learning a set pair O8 - value paid which is updated manually by the finance staff $J8 - is set to 1 if student is entitled to free school meals$K8 - is set to 1 is the instrument is being learnt as part of the student's GCSE course It seems to me that the problem is that the multiplication operation at the end of the formula is screwing it as it seems to multiply something by 0 if certain conditions are met or by 1 if they are not. Multiplying a negative with a positive number will always give a negative but multiplying with zero will always give zero... I think that's what's breaking it but I struggled to figure out how to fix it so credit amounts show as negative and not as zero until I self-bodged what i think is a very clumsy "solution" as per below: I created two hidden columns. I moved the original formula in column P and removed the final multiplication statement: - *IF(SUM($J8:$K8)>0,0,1) - to column Q and hid the column. Added a second formula in column R and hid the column: - =IF((COUNT(J6:K6)>0),0-O6,Q6) - which looks at whether the student is due free lessons or not by checking for numbers in the relevant columns The amount owed column (P) now has this - =IF(ABS(R7)-O7<=0,R7,ABS(R7)). This makes sure the amount owed is shown in the correct sign (either negative or positive) - if the school is owed the amount is positive, if the student is owed the amount is shown as negative and in red. It works but it seems to me to be very clumsy I'm sure there is a way of doing this without resorting to using up two extra columns of data. If anyone has any suggestions please feel free to embarrass me! Treat me kindly though as I am very new to this formula lark and am learning on the job..... Literally. Thanks in advance....

Data Manager opportunity for a leading Independent school. Great benefits and salary package on offer. Salary on offer is £40,000 (Pro-Rata), 42 working weeks per annum. Salarty £36,000. Duties and Responsibilities - · Manage and administer the schools MIS systems and producing custom reports. · Managing all pupil reports and creating custom reports. · Provide support and guidance for all GDPR compliance matters. · Managing the schools learning platform, room booking and parent portals. · Administration of pupil data for target setting and analysis of examination data. · Providing IT support and assistance to the Examinations Manager Skills and Experience - · Working with Management Information systems. · Data analysis tools. · Advanced Microsoft Office skills. · Ability to work under pressure and working independently. · Experience working in a school is desirable. · SQL experience is desirable. APPLY NOW – Submit your CV to [email protected] or contact us today on 07904 015757 to discuss the opportunity further. GreatFind Recruitment is a London based recruitment agency specialising within the IT and EdTech sectors. We pride ourselves on our strategic, consultative approach, delivering tailored recruitment solutions that meet your goals. We help you grow, whether you’re exploring your career options or building your own team. Ravi Patel GreatFind Recruitment 19 Years Experience working with Schools! Tel - 020 3318 2128 Out of hours - 07904 015757 Email - [email protected] For our latest vacancies visit - http://www.greatfindrecruitment.com Follow us on LinkedIn - https://www.linkedin.com/company/greatfind-recruitment/ **APPROVED**

Dear All, I have students choosing their GCSE options via a Google Form which then populates their options in a google sheet. I then break down the choices into each separate subject sheets. What I want to do is create an automatic list i.e. when somebody chooses art then their name is populated in the art sheet. I hope this makes sense. Is there a formula that can help with this? I look forward to any responses to my query. Many Thanks James

Morning I have this formula below working on a spreadsheet. Its that old I cant even remember how i created it. But it works for what i need. I just want to manipulate it to count numbers between 20 and 39, but no matter what I try I cant get it to work. Can anyone help? =SUMPRODUCT(SUBTOTAL(3,OFFSET(AI3:AI875,ROW(AI3:AI875)-MIN(ROW(AI3:AI875)),,1))*(AI3:AI875>=20))

Morning Everyone, I am wondering if anyone else has come across the same issue as ourselves. We installed some new PCs over summer with Windows 7 and Office 2010. We also rebuilt some existing machines with the same config. All these machines, when you select clipart in the Office apps, and choose Find more at Office.com, are getting this web page error: http://office.microsoft.com/en-gb/images/?CTT=6&ver=14&app=winword.exe. Anyone else had this and managed to solve it? By the way, I have added *.microsoft.com, microsoft.com, office.com and office.microsoft.com into the Sophos firewall but it doesn't look like a firewall issue - it looks more an MS link issue to me. Thanks A

Got a bit of a wierd problem when using Excel 2003. The user has 3 or 4 different sheets open, as well as other applications, word, IE, 3rd party DB application. At random times, suddenly the user will switch to excel to work and all the cells and text/numbers within the cells is massive (like font 80 or something) and the sheet is completely unreadable. the windows are still operating and you can switch between apps and between excel sheets, but you can't get the graphics back to normal. The user has to close the sheets and then excel crashes (send error report?) and then you can reopen the sheets and carry on. I have never seen anything like this - it doesn't seem like graphics card error, as all the other windows and apps behave normally. Don't know if it is excel corruption or what else to consider - have already reinstalled office!

Head of IT has given me an interesting task, to write a macro to convert the school calendar to iCal format to put into Outlook. Currently it is in an Excel spreadsheet and I've done a bit of work with macros before to shade cells in different colours for reports. But has anyone ever tried to do this? I assume I have to capture the data, build it into one huge variable and dump the contents in a text file somewhere?

Hello All, Imagine the spreadsheet: ... A B 1. 0 J 2. 1 K 3. 1 J 4. 0 L 5. 1 L I want a formula that states: Count all the cells in column B that contain "J" IF column A = 1. So the result of the formula for the above sheet would be 1 (cell B3 only). I'm trying to knock together a formula with COUNTIF and the AND operator without much success. Can someone give me a pointer please? Thanks in advance, Ben

Having a mental block here - must be something to do with the heat! I have a list of "surname space forename" in one column. I need to separate them into two columns - how do I detect the space in order to capture each part, to place in separate columns?

im trying to make a summary sheet where you can select a reporting period in the first list box, but then in the second list box it only shows the avaliable options for that reporting period chosen in the first list box? and then display results using a vlookup for that particular reporting period and subject. also if all is selected in the first list box then everything is show in the second list box any ideas ? thanks James

Hello All I'm creating a small calendar in Excel and i was wondering whats the best way to input data in to my table. So far my ideas to have a data input form where i select a name and enter my custom date with my custom text. My logic behind this on my form i select "Tom" from a a drop down, and then i insert the date "16/02/2012" and in my custom text box i would enter in something like SF which should populate cell E6 with SF. (see example below) I've got very basic VBA skills which i did in collage way back but i don't putting some effort in to learning.

I have a spreadsheet where I have an IF function in one of the columns. The type of IF function is dependent upon whether the entrants are Male or Female, as they have different sports results. Can I use a VLOOKUP function to enter the correct IF function into the specified cell when either 'Male' or 'Female' is typed into another cell? If so, my IF functions have specified cells in them (obviously for IF it contains a certain number, display this text) so what can I put instead of specific cell numbers that will get the correct ones each time? I hope this makes sense!

Hi all I have a feeling that the answer to this is going to be far more complicated that I realise but ... Datahandling is often a difficult topic to teach and not helped by software that makes a mountain out of a molehill (IMHO). I know that Access is very powerful but is often a sledgehammer to crack a nut. Most pupils can get on OK with excel but it's difficult to edit single records or to create queries and reports. It occurs to me that if you could have a nice 'form' tool that connects to an excel back end then this would overcome some problems. Something cool may even offer xml conversion, searching and queries. Something really cool may allow multiple users to access a single excel file (or maybe connect easily to a SQL server) fo that you can have multiple input. Has anyone ever seem anything like this? Bob