plant
New Member
Posts: 79
|
Post by plant on Sept 28, 2022 20:41:38 GMT
Evening all
Im having a mare and need some excel help from someone who knows what the fuck they're doing.
I know this is a none optimum way to solve the problem, but I'm stuck with it.
f(Assessment Mark <=39, “Fail”, If(and(Assessment Mark >=40, <=60),”Pass”,
If(and(Assessment Mark >=61, <=80),”Merit”, If(and(Assessment Mark
>=81,<=100),”Distinction”))))
That's an excel formula that needs to be applied to a piece of coursework. Every time I try and apply it or a variation with direct cell references, I get a syntax error.
It refers to an adjacent cell with an integer value of 0-100
Can anyone explain why it's not working?
Thanks- a very frustrated school data manager
|
|
|
|
Post by 😎 on Sept 28, 2022 20:55:46 GMT
Don’t think you need the Ands there or the nested between values
This worked:
IF(scorecell <40, “fail”, IF(scorecell <61, “pass”, IF(scorecell <80, “merit”, “distinction”)))
|
|
plant
New Member
Posts: 79
|
Post by plant on Sept 28, 2022 21:10:49 GMT
Thanks for looking. The exam board is insisting we use the equation as written for the class's coursework. Your method is very similar to the one I suggested the class teacher use.
Any idea why the original didn't work?
|
|
|
|
Post by Chopsen on Sept 28, 2022 21:20:36 GMT
Well if they're insisting on using a formula that doesn't work the result is an error.
If they want a meaningful outcome, you're going to need to change the formula. Because it doesn't work.
|
|
|
|
Post by Chopsen on Sept 28, 2022 21:31:30 GMT
...though the reason it's broken is probably because that's not AND works in excel.
It's AND (A1>1,A1<4) to find if A1 is 2 or 3. Not AND (A1>1,<4)
|
|
|
|
Post by Reviewer on Sept 28, 2022 21:37:03 GMT
Yeah, you need the reference going in each time like chopsen says. It’s not the best way, gremmi’s is, but it will work.
|
|
mrpon
Junior Member
Posts: 3,737
|
Post by mrpon on Sept 28, 2022 21:39:15 GMT
Any idea why the original didn't work? If is spelt wrong?  |
|
plant
New Member
Posts: 79
|
Post by plant on Sept 29, 2022 10:09:03 GMT
Thanks all
Appreciated.
|
|
Deleted
Deleted Member
Posts: 0
|
Post by Deleted on Sept 18, 2023 12:22:03 GMT
Can this be a general rescue thread please?
If anybody could be so kind…
I have two workbooks.
Workbook 2 needs to pull information from workbook 1 in a table from cells B1:B8 (text values) . So I’m just typing equals, and then clicking the appropriate cell on the other workbook. This works to an extent and I’m seeing this:
=‘[Workbooktitle.xlsx]Worksheet title’!$B$1
I want to do this over a row on workbook 2 that returns values from B1 to B8. However absolute reference with the dollar on column only is having no effect; so when I copy across I’m getting the B1 value returned 7 more times. Removing the absolute reference altogether returns B1,C1,D1 etc which is not what I need.
Potentially poorly explained sorry but if any whizzes get it you could save me hours.
|
|
|
|
Post by Reviewer on Sept 18, 2023 12:40:20 GMT
Sounds like you need to remove just 1$ sign. The first if you want the row number to stay the same, the second if you want the column letter to stay the same.
If you’ve done that try pressing F9
|
|
Deleted
Deleted Member
Posts: 0
|
Post by Deleted on Sept 18, 2023 13:20:45 GMT
Sounds like you need to remove just 1$ sign. The first if you want the row number to stay the same, the second if you want the column letter to stay the same. If you’ve done that try pressing F9 Had tried the former, was hoping the latter would be the magic bullet, alas no. Thanks though. |
|
Deleted
Deleted Member
Posts: 0
|
Post by Deleted on Sept 18, 2023 13:30:07 GMT
So to expand, I am doing this exercise 20 times across 20 worksheets.
I’ve fixed it by manually changing the cell references for one full row of the linked row. Copy and paste that and the absolute reference thing is sorted 20 times but it’s all referencing sheet 1.
Then on subsequent ones done a find and replace of the worksheet title number.
Not the most eloquent solution but it’s working.
|
|
