If you need urgent support, call 999 or go to your nearest A&E. To contact our Crisis Messenger (open 24/7) text THEMIX to 85258.
Options
Excel formula problem
Former Member
Posts: 1,876,323 The Mix Honorary Guru
in General Chat
Hello... I've been having a problem with excel and i was wondering if anyone familiar with formulas in excel could have a look at one I've written. It's fairly simple and it is about using the IF statement. Here's the formula.
In the attachments, I have a picture of the preview of the sheet. To understand the different boxes, i've coloured them and i've provided a key to make helping me a lot easier (not that it will ever be, my apologies
):
from left to right:
Dark blue (7) = B7
Red (0) = C5
Pinky (12.5) = F5
Light blue (5) = G7
Yellow ("M") = I6
Dark green (should be measurement for M, which = moment) = J6
light green ("F") = L6
purple ("D")= O6
the measurements for F and D are (F)= M6 (D)= P6
The formula i wrote applies for the line under (substitute 6 with 7) - i.e. the line under the colour rings. What i want my formula to do is to check all the units first; Nm under dark green, N under green and one to the right and m under purple and one to the right.
My question is... is the formula incorrect in any way? have i missed something or done too many brackets? The problem is, I think the actual problem lies in my software. I've previously tried making such long IF formulas before, and when i've exceeded like 6 IF statements, the formula stops working for some reason (although this only happened at that occasion... I'm still dubious whether this problem is still present and that my formula isn't working because I have so many IF statements that it won't work.
Appreciate any help.
By the way, I'm writing a physics worksheet on moment and turning forces. The formula should be M = D * F. data!$D2 = "correct", whereas data!$D3 = "incorrect".
-Deep Fathom
=IF(J7="";"";IF(J7="Nm";IF(M7="";"";IF(M7="N";IF(P7="";"";IF(P7="m";IF(L7=B7*10;IF(O7=C5;IF(I7=L7*O7;data!$D2;data!$D3);data!$D3);data!$D3);data!$D3))))))
In the attachments, I have a picture of the preview of the sheet. To understand the different boxes, i've coloured them and i've provided a key to make helping me a lot easier (not that it will ever be, my apologies
):from left to right:
Dark blue (7) = B7
Red (0) = C5
Pinky (12.5) = F5
Light blue (5) = G7
Yellow ("M") = I6
Dark green (should be measurement for M, which = moment) = J6
light green ("F") = L6
purple ("D")= O6
the measurements for F and D are (F)= M6 (D)= P6
The formula i wrote applies for the line under (substitute 6 with 7) - i.e. the line under the colour rings
. What i want my formula to do is to check all the units first; Nm under dark green, N under green and one to the right and m under purple and one to the right.My question is... is the formula incorrect in any way? have i missed something or done too many brackets? The problem is, I think the actual problem lies in my software. I've previously tried making such long IF formulas before, and when i've exceeded like 6 IF statements, the formula stops working for some reason (although this only happened at that occasion... I'm still dubious whether this problem is still present and that my formula isn't working because I have so many IF statements that it won't work.
Appreciate any help.
By the way, I'm writing a physics worksheet on moment and turning forces. The formula should be M = D * F. data!$D2 = "correct", whereas data!$D3 = "incorrect".
-Deep Fathom
0
Comments
Are you sure you're referencing the right cells? You can rename the cells (ie give them words as names rather than B7) by clicking on the cell and then typing your name for it into the little white box to the left of the formula box. Doing that helps you find any errors in the formula.
Good luck
xx
have you just got to fill in the gaps on your worksheet? or are you writing an interactive excel sheet for x values?
I dont quite understand what you're trying to achieve with the IF statement. are you comparing your value of angular momentum with values $D2 & $D3 to see if they're correct?
Exactly. I've been working on a worksheet for kids quite below my age (hence the simple level of the questions, although this question is one of the easy ones).
Sorry, I just realised how confusing it must have been. I have a seperate sheet where I have two boxes, one i've written "correct" and the other "incorrect". The correction box beside the calculation in the Question 3 sheet literally becomes "data!$D3" or "data!$D2" depending on if the IF statement is true or false.
There is a seperate box beside the calculation, which "corrects" the calculation - it has to check the value and the units. It firstly checks if the box is empty - if nothing has been typed in, it will equal "", or nothing. However, if it is not "" (i.e. nothing written in), then the IF statement will run. The IF statement checks if one box is correct, and then checks the next boxes until all have been done (this is done in a sort of order, where the next IF statement is run only if the first is correct... if one is wrong, that step of the calculation is wrong, and if one is empty (i.e. has not been filled in yet, nothing shows up... otherwise, when they are in the process of completing the question, it will show "incorrect" -- i'll be using these boxes at the end where i'll have a result sheet, which uses SUM and is linked to the different boxes correcting all steps for the calculation (for example, if the final answer is correct but the units are wrong, they might get 2 points out of three etc etc)
I'm afraid I've only really used excel for data analysis. but, that said, you may find it easy split the function up if it's not working, and have an individual IF statement for each x that the student puts in.
rather than having the testing of the second question only run if the first question is correct, have them all individually checked, and stick data!$D3 or D4 next to it.
if you get that to work, it should be easier to then combine all of your functions so that answer 2 is only checked once answer 1 is checked etc.
it will also make it easier to trouble shoot a series of similar, but smaller functions, than one big one :thumb:
Yea, I'm going to experiment with different approaches tonight. I'm quite positive with your idea, trying to break it down so it becomes easier to handle later on. Right now, i've got this effing workload i seriously have to finish off... sigh. Home economics really feels like a waste of time. All these hypothetical situations... are almost comical! :razz:
Also, it uses commas to seperate the parts, not semicolons.
So you'd probably have to split out the calculations if you need more than 6 tests.
oooh get you
As far as I'm aware, aren't different arguments in an IF statement seperated by commas and not semicolons? So it should be:
=IF(B4=1,"One","Not one")
If you're still stuck I'm more than happy to help some more
edit: ooh im an idiot and didnt read mists reply