Search the Community

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.

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....

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

Hi all, I have an annoying problem that I have been trying to find a work around for the past few hours! I have a master excel spreadsheet with individual spreadsheets linking to the master. The problem is, I don't want users having access to viewing the master sheet, just their own faculty one. If I lock the master to stop public viewing, the individual sheets can't update unless the user enters the password for the master. Is there something silly that I have missed, or is there an easier way to do this? Many thanks in advance.

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?

Would anyone like to point out why the following is invalid according to progresso? It was entirely built by clicking selections in the command builder. IF(((Count(Y7 2016 entry.Cur. Threshold * 1)) = (0)),Y8 2016 entry.Cur. Threshold * 1,Y7 2016 entry.Cur. Threshold * 1)

PLEASE HELP! This is driving me crazy! I'm trying to create data entry columns in SIMS Assessment Manager templates, and I really need them to change colour depending on the data I type into them. For example, I have created a column which has a grade set A, B, C and I would like the cell to change blue when I type A, red when B, etc. FYI, I understand how to use NESTED IF THEN ELSE formulas. In the past I have created a data entry column and then I have made another data entry column with a NESTED IF THEN ELSE formula. The second column looks for data in the first column and then changes colour depending on the data entered in the first column. However, for my current project, I need 1 column which can do both - a column in which data can be entered AND it changes it's colour depending on the data I have entered into it. It just seems impossible! I hope this makes sense! If anyone can help I'd really appreciate it! Thanks

Hi Guys, wondering if anyone can help me spot what im doing wrong. I have 3 columns and a lookup table Column 1 Contains a Grade (A,B,C etc) Column 2 Lookups up the Value of that grade, with an iferror to stop #N/A =IFERROR(VLOOKUP([@Maths],Grade_to_Points,2,FALSE),"") Column 3 looks at Column 2 and if the value is greater than 5 return M =IF([@[Maths Points]]>=5,"M","") Now the problem I have is Column 3 returns M for every Cell, even though column 2 cells are blank...... what am I missing? any help much appreciated. thanks James

Is there any way I can use a formula column in SIMS to return the highest result for each pupil from 2 columns. I have been messing about with the MAX formula column but can't seem to get it to work with that.

If I write a formula everything appears to be working. However if I then either edit, copy, move, or in any way, shape or form touch that formula excel is re-formatting the cell to text. This isn't happening consistently, sometimes it works fine and as it should, sometimes it doesn't. Our IT Support cannot work out what is causing the issue, how to stop it (other than manually formatting the cells when the issue occurs) and why it is frequent, but not consistent. **just to add - it doesn't seem to be related to the complexity of the formula.

Hi there, I'm absolutely rubbish with Excel and have been asked to work out some details regarding potential exam results. I need a formula for each subject pass % grade 4 to 9 and then an overall cohort % pass rate 4 - 9. I have played about with it but I'm getting nowhere. Any guidance/assistance would be great. Thanks in advance.

A colleague is working on an Excel spreadsheet for Target GCSE grades, she has formulas for most things, however, we really need to see if there is a formula for the time taking bit! Each Grade in columns C through to Z are worth a certain amount of points, (A1=58; A= 52; B=46; C=40; D=34;E=28;F=22;G=16 which is the GCSE point scoring system) column AG is the total scored for all the GCSEs taken – for example Student 2 scored 372, he took 9 GCSE, but we want the score for the top 8 grades, so we subtract the lowest GCSE score which is ‘D’ worth 34 points this is shown in column AH. Total Points score subtract AF-2 should equal Total Points Score (Best 8) *Could a formula be created to work out the score of the bottom GCSE grades, ie: if they took 10 GCSE’s you would want a formula to subtract the lowest 2 grade scores and insert the result in column AH. *Could a formula be created to subtract the AF-2 from the Total Points Scored? I’m afraid I’ve never had to work out such complicated formula’s but if someone can help that would be wonderful Oh and if you don't read this until even months later and you think you can help, please reply, because it would just be wonderful to have the formula for future GCSE Gradesheets. Thank you Kirsty KPformula.xls

Hi all, I'm hoping someone can help me with this: We want to start sending reports home with colour coding instead of grades which we will extract from an aspect in a marksheet. However, I'm not sure how to create a column that will remain blank but will colour depending on a grade that is entered into a different column. For example: [TABLE=class: grid, width: 400] [TR] [TD]Name[/TD] [TD]Strand A01[/TD] [TD]Strand A01 Colour[/TD] [/TR] [TR] [TD]Bloggs, Fred[/TD] [TD]Emerging[/TD] [TD](blank but coloured red)[/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [/TABLE] 'Strand A01 Colour' is the aspect that I would pull through into the report. I am familiar with formula columns and have tried a few to no avail. Can anyone help? Thanks!

DOES ANY ONE KNOW WHAT FORMULA I COULD USE TO CONCATENATE FOUR CELLS BUT ALPHABETICALLY, so it ends up like the last column [TABLE=width: 500] [TR] [TD][TABLE=width: 168] [TR] [TD=class: xl66, width: 168]History[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]HOSPITALITY AND CATERING[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]SPANISH[/TD] [/TR] [/TABLE] [/TD] [TD][TABLE=width: 176] [TR] [TD=class: xl66, width: 176]Photography[/TD] [/TR] [/TABLE] [/TD] [TD]history hospitality and catering photography spanish[/TD] [/TR] [TR] [TD]Maths[/TD] [TD]French[/TD] [TD]Geography[/TD] [TD]Art[/TD] [TD]Art French Geography Maths[/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [TR] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [/TR] [/TABLE]

Hi I have a large table of data like the dummy data below that i am trying to reorganise by using a formula or macro (which ever is best). The data consists of students and registration groups etc. I want to split the data into registration groups to ultimately be able to print the data with one registration group per page. Any suggestions how i would achieve this? Thanks in advance.

Hello All, Fairly advanced Excel formula help please... I want to count the number of cells in Column B that = "A", "B", "C" IF Column A = "A", "B", "C" I can't get a SUMPRODUCT to work with multiple criteria in both columns (or a wildcard in one column). I have tried: =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"})) =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="*")) Neither work. I could use an intermediate calculation, but I'm trying to avoid it. I could use: =SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))+SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="B"))+... but that's messy. Anyone any ideas? Thanks in advance