akinkaraman
Member
In this excell file G6, AA6 and BO6 cells give errors that I couldn't understand.
Can you fix those?
Thanks in advance..
Can you fix those?
Thanks in advance..
Need Help.. 3 cells have error.. why?
- Thread starter akinkaraman
- Start date
Somendra Misra
Excel Ninja
Hi akin,
Well in your file on the mentioned cell you have formula something like =#N/a
Than the formula you posted below is in language which many of the forum user don't understand. Kindly, post those formulas in English.
Than, you name manager shows many named ref. or formula which all shows #REF error. If they are part of these formulas, than correct them and than post a file.
Regards,
Well in your file on the mentioned cell you have formula something like =#N/a
Than the formula you posted below is in language which many of the forum user don't understand. Kindly, post those formulas in English.
Than, you name manager shows many named ref. or formula which all shows #REF error. If they are part of these formulas, than correct them and than post a file.
Regards,
NARAYANK991
Excel Ninja
Hi ,
I assume that the formula in the 3 cells you have mentioned would be on the same lines as the formulae in the other cells ; however , the formulae you have posted against the cell addresses are all partial formulae ; they are not complete.
I think what has happened is that someone has pressed F2 , F9 , so that the formulae in those cells have been replaced by their values ; the only way you can put back formulae in those cells is to have the full formulae.
Secondly , all the formulae in row 6 are referring to cells in row 5 ; I do not know why you have shown the formulae in the 3 cells referring to cells in row 50 ; is this correct ?
Lastly , there are too many IF functions used ; it would be better if you could set up a lookup table , and use either a VLOOKUP or LOOKUP or INDEX/MATCH to simplify the formula.
Narayan
I assume that the formula in the 3 cells you have mentioned would be on the same lines as the formulae in the other cells ; however , the formulae you have posted against the cell addresses are all partial formulae ; they are not complete.
I think what has happened is that someone has pressed F2 , F9 , so that the formulae in those cells have been replaced by their values ; the only way you can put back formulae in those cells is to have the full formulae.
Secondly , all the formulae in row 6 are referring to cells in row 5 ; I do not know why you have shown the formulae in the 3 cells referring to cells in row 50 ; is this correct ?
Lastly , there are too many IF functions used ; it would be better if you could set up a lookup table , and use either a VLOOKUP or LOOKUP or INDEX/MATCH to simplify the formula.
Narayan
akinkaraman
Member
Sorry to send you wrong file.. The formulas will be like those :
for G6
=IF(X50=0;0;IF(X50<201;-1;IF(X50<501;-3;IF(X50<1001;-5;IF(X50<1501;-8;IF(X50<2001;-11;IF(X50<2501;-13;IF(X50<3001;-16;IF(X50<3501;-19;IF(X50<4001;-21;IF(X50<4501;-24;IF(X50<5001;-27;IF(X50<5501;-29;IF(X50<6001;-32;IF(X50<6501;-34;IF(X50<7001;-37;IF(X50<7501;-40;IF(X50<8001;-42;IF(X50<8501;-45;IF(X50<9001;-48;"N/A"))))))))))))))))))))
for AA6
=IF(AR50=0;0;IF(AR50<201;-1;IF(AR50<501;-2;IF(AR50<1001;-3;IF(AR50<1501;-5;IF(AR50<2001;-6;IF(AR50<2501;-8;IF(AR50<3001;-10;IF(AR50<3501;-11;IF(AR50<4001;-13;IF(AR50<4501;-15;IF(AR50<5001;-16;IF(AR50<5501;-18;IF(AR50<6001;-19;IF(AR50<6501;-21;IF(AR50<7001;-23;IF(AR50<7501;-24;IF(AR50<8001;-26;IF(AR50<8501;-28;IF(AR50<9001;-29;"N/A"))))))))))))))))))))
for BO6
=IF(CF50=0;0;IF(CF50<201;0;IF(CF50<501;1;IF(CF50<1001;1;IF(CF50<1501;2;IF(CF50<2001;3;IF(CF50<2501;3;IF(CF50<3001;4;IF(CF50<3501;5;IF(CF50<4001;5;IF(CF50<4501;6;IF(CF50<5001;6;IF(CF50<5501;7;IF(CF50<6001;8;IF(CF50<6501;8;IF(CF50<7001;9;IF(CF50<7501;10;IF(CF50<8001;10;IF(CF50<8501;11;IF(CF50<9001;12;"N/A"))))))))))))))))))))
Those formulas give #N/A
What is wrong with those?
Thanks..
for G6
=IF(X50=0;0;IF(X50<201;-1;IF(X50<501;-3;IF(X50<1001;-5;IF(X50<1501;-8;IF(X50<2001;-11;IF(X50<2501;-13;IF(X50<3001;-16;IF(X50<3501;-19;IF(X50<4001;-21;IF(X50<4501;-24;IF(X50<5001;-27;IF(X50<5501;-29;IF(X50<6001;-32;IF(X50<6501;-34;IF(X50<7001;-37;IF(X50<7501;-40;IF(X50<8001;-42;IF(X50<8501;-45;IF(X50<9001;-48;"N/A"))))))))))))))))))))
for AA6
=IF(AR50=0;0;IF(AR50<201;-1;IF(AR50<501;-2;IF(AR50<1001;-3;IF(AR50<1501;-5;IF(AR50<2001;-6;IF(AR50<2501;-8;IF(AR50<3001;-10;IF(AR50<3501;-11;IF(AR50<4001;-13;IF(AR50<4501;-15;IF(AR50<5001;-16;IF(AR50<5501;-18;IF(AR50<6001;-19;IF(AR50<6501;-21;IF(AR50<7001;-23;IF(AR50<7501;-24;IF(AR50<8001;-26;IF(AR50<8501;-28;IF(AR50<9001;-29;"N/A"))))))))))))))))))))
for BO6
=IF(CF50=0;0;IF(CF50<201;0;IF(CF50<501;1;IF(CF50<1001;1;IF(CF50<1501;2;IF(CF50<2001;3;IF(CF50<2501;3;IF(CF50<3001;4;IF(CF50<3501;5;IF(CF50<4001;5;IF(CF50<4501;6;IF(CF50<5001;6;IF(CF50<5501;7;IF(CF50<6001;8;IF(CF50<6501;8;IF(CF50<7001;9;IF(CF50<7501;10;IF(CF50<8001;10;IF(CF50<8501;11;IF(CF50<9001;12;"N/A"))))))))))))))))))))
Those formulas give #N/A
What is wrong with those?
Thanks..
Last edited:
Sathish KV
Active Member
Hi,
I could not find any error in your formula. Its working fine!
I could not find any error in your formula. Its working fine!
akinkaraman
Member
Can you send me the file because when I write these formulas into related cells, it gives error and I can not save the file.
Sathish KV
Active Member
Hi,
There is no formula written where ever there is an #N/A error
In cel G6, AA6, etc.. there is no formula instead it is written as =#NA.
Check it out and write the correct formula
There is no formula written where ever there is an #N/A error
In cel G6, AA6, etc.. there is no formula instead it is written as =#NA.
Check it out and write the correct formula
akinkaraman
Member
As I told before I am writing but it gives error and saves like this. It is written as #N/A
Please save the working formulas and please put the file here to let me see.
Thanks..
Please save the working formulas and please put the file here to let me see.
Thanks..
Sathish KV
Active Member
Hi,
Have uploaded the file
Have uploaded the file
akinkaraman
Member
Formulas are wrong in your file.
I want to make below formulas..
You just copy the next cells's formulas, which is not correct.
Each one are different.
Only these 3 formulas are making errors in this sheet, other cells have no problem..
I want to make below formulas..
You just copy the next cells's formulas, which is not correct.
Each one are different.
Only these 3 formulas are making errors in this sheet, other cells have no problem..
Sathish KV
Active Member
Hi,
Was this file created as .xls format or converted to .xls format from .xlsx
Was this file created as .xls format or converted to .xls format from .xlsx
akinkaraman
Member
I don't know but I think made in Open Office than excel.
Sathish KV
Active Member
Hi,
One issue was the formula contains semi-colon instead of comma
Try to change it
But even after converting I am getting an error in 2003 but works fine in excel 2007
One issue was the formula contains semi-colon instead of comma
Try to change it
But even after converting I am getting an error in 2003 but works fine in excel 2007
bobhc
Excel Ninja
akinkaraman
Member
So How will I solve this problem?
3 cells have still formula errors..
3 cells have still formula errors..
akinkaraman
Member
Q6 AND G6 have not got same formula.. If you copy it to G6, it also changes the formula for G6 which makes wrong data..
I tried to do that then change the formula back manually but it again gave error.
I tried to do that then change the formula back manually but it again gave error.
A few questions:
There is no relationship or link between G6 and Q6 so I don't understand your above statement "If you copy it to G6, it also changes the formula for G6 which makes wrong data"
Your formula posted above for G6: references Row 50 ? I am sure it should be 5
All the other Formulas in Row 6 link to the cell directly above them in Row 5, why shouldn't the other 3?
What version of Excel are you using ? Because Excel 95-03 don't support more than 7 levels of nested If's which your formulas have. You shuld use *.xlsx format.
There is no relationship or link between G6 and Q6 so I don't understand your above statement "If you copy it to G6, it also changes the formula for G6 which makes wrong data"
Your formula posted above for G6: references Row 50 ? I am sure it should be 5
All the other Formulas in Row 6 link to the cell directly above them in Row 5, why shouldn't the other 3?
What version of Excel are you using ? Because Excel 95-03 don't support more than 7 levels of nested If's which your formulas have. You shuld use *.xlsx format.
Somendra Misra
Excel Ninja
@akinkaraman
See the file, your formulas contain more no. of allowed nested IF. If you try pressing F2 and press enter in the cell where you got value, Excel will not allow you to do that. Yellow cell has formula. I had created a helper table and used VLOOKUP function.
Regards,
See the file, your formulas contain more no. of allowed nested IF. If you try pressing F2 and press enter in the cell where you got value, Excel will not allow you to do that. Yellow cell has formula. I had created a helper table and used VLOOKUP function.
Regards,
akinkaraman
Member
I will try to convert it to .xlsx format. It sounds sense.
I wrote the formulas there wrongly at that time. I think you didn't see my second file after that.
I am pasting it below again..
I wrote the formulas there wrongly at that time. I think you didn't see my second file after that.
I am pasting it below again..
Somendra Misra
Excel Ninja
See the file I attached in my comment #20. Hope this meets your requirement.
Regards,
Regards,
akinkaraman
Member
Hi Somendra, I checked up; vlookup seems to work normal at Quick Office with iPad.
I will make all cells like that due to your formula.. Thanks..
I will make all cells like that due to your formula.. Thanks..
Somendra Misra
Excel Ninja
Try it on Ipad & Welcome Back.
Regards,
Regards,
shrivallabha
Excel Ninja
I'd guess it has to do with the nesting levels limit for IF. It was 6 or 7 levels in Excel 2003. And it has been made 64 starting from 2007. Nesting level numbers could be wrong as I have not checked.
Edit: Hui has already noted this! Sorry for unnecessary post.