I have an if statement that does not show the proper value if it is true. If I go to the formula screen (picture attached), it shows that the equation is true and therefore should return a "1", but instead the formula returns a "0". The Value_if_False is not even set to 0. I have tried using the Evaluate Formula option, and it shows that the formula is true, but it still returns a value of "0". Please Help!
excel_error.png
Last edited by cemont; 03-02-2012 at 12:32 PM.
This picture isn't much help, only part of the formula is displayed.
Post your worksheet itself, that way we can determine the values exactly.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
cemont,
Welcome to the forum!
It could be that you have the workbook set to manual calculation. Try pressing F9 to force a calculate and see if that updates the formula result.
If that doesn't fix it, then...
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hello
is the formula array???
ctrl + shift + enter
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
I was trying to avoid posting the spreadsheet, because there are 38 columns with 11 rows worth of formulas (if statements, vlookups, etc.) But I can post it if necessary.
The formula that I am having trouble with is as follows:
=IF(OR(AND($Z$19>1,AB9=1),AND($AC$19>1,AE9=1),AND($AF$19>1,AG9=1),AND($AH$19>1,AJ9=1),AND($AK$19>1,AM9=1),$AN$19>1),1,2)
I know the formula works because the formula result (as shown in the bottom left corner of the picture) is correct. Also, I've run the Evaluate Formula option and it was correct until the very end. Somehow, it goes from "=IF(TRUE,1,2)" and returns a final evaluation of 0.
Go on, post it
Here is a dummy version without headings and vlookups (bc they refer to other spreadsheets). Just to warn you, there are a lot of IF statements. The last formula which is highlighted in yellow is the one that doesn't work properly. To answer previous questions: No it is not an array, and if I press F9, it does display the correct result, but it deletes the formula. I need the formula to remain and for the calculation to work automatically. Thanks!
Last edited by cemont; 03-02-2012 at 04:50 PM.
cemont,
The problem is that you have a circular formula in your AL column. The formula references itself when trying to calculate. I did notice that its comparing against the max in that column, and the numbers you have for the output in that column are 0 and 1, so I changed the formula in AL8 (and copied down) to be:
=IF(AND(J8=1,AK8=1),1,0)
Then the formulas in the AS column calculated correctly.
Thanks tigeravatar! Those cells were supposed to compare the AK to the max in the AK column not the AL column. That must have happened when I transferred part of the spreadsheet over. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks