Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbers, text
Hi
Any help? I have this formula in E8, f8, to x8 sheet2: =IF(OR(ISNUMBER('sheet1'!E8);ISTEXT(' sheet1'!E8));' sheet1'!E8;"") In AH8 I have this formulae, as array formulae: =IF(E8<50;$E$7;"")&" "&IF(F8<50;$F$7;"")&" "&IF(G8<50;$G$7;"")&" "&IF (H8<60;$H$7;"")&" "&IF(I8<60;$I$7;"")&" "&IF(J8<60;$J$7;"")&" "&IF (K8<60;$K$7;"")&" "&IF(R8<50;$R$7;"")&" "&IF(S8<50;$S$7;"")&" "&IF (T8<50;$T$7;"")&" "&IF(U8<60;$U$7;"")&" "&IF(V8<60;$V$7;"")&" "&IF (W8<60;$W$7;"")&" "&IF(X8<60;$X$7;"") Which checks the score and gives the name of the subject if it does not meet the if check It works perfectly when there is only numbers, but sometimes I have instead of a score (number), a text such as (absent), and I want to return the name of the subject like ($E$7). In E8 could be a number or a text, or it can be empty the result of the first formula, so I tried this =IF(OR(E8<50;ISTEXT(E8));$E$7;"")&" &IF(OR(F8<50;ISTEXT(F8));$F$7;"") &" "&IF(OR(G8<50;ISTEXT(G8));$G$7;"")&" "&IF(OR(H8<50;ISTEXT(H8));$H $7;"")&" "&IF(OR(I8<50;ISTEXT(I8));$I$7;"")&" "&IF(OR(J8<50;ISTEXT (J8));$J$7;"")&" "&IF(OR(K8<50;ISTEXT(K8));$K$7;"")&" "&IF(OR (R8<50;ISTEXT(R8));$R$7;"")&" "&IF(OR(S8<50;ISTEXT(S8));$S$7;"")&" "&IF (OR(T8<50;ISTEXT(T8));$T$7;"")&" "&IF(OR(U8<50;ISTEXT(U8));$U$7;"")&" "&IF(OR(V8<50;ISTEXT(V8));$V$7;"")&" "&IF(OR(W8<50;ISTEXT(W8));$W$7;"") &" "&IF(OR(X8<50;ISTEXT(X8));$X$7;"") But it gives all the names of the subjects even if it is empty since the formulae is giving which is text, what can I do to make it work with numbers, text or . Thanks in advance Jam |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
numbers, text
Hi,
How about =IF(ISNUMBER(Sheet2!E8),Sheet2!E8,"") And unrelated you could simplify your other formula to =IF(E8<50,$E$7," ")&IF(F8<50,$F$7," ")&IF(G8<50,$G$7," ")&IF(H8<60,$H$7," ")&IF(I8<60,$I$7," ")&IF(J8<60,$J$7," ")&IF(K8<60,$K$7," ")&IF(R8<50,$R$7," ")&IF(S8<50,$S$7," ")&IF(T8<50,$T$7," ")&IF(U8<60,$U$7," ")&IF(V8<60,$V$7," ")&IF(W8<60,$W$7," ")&IF(X8<60,$X$7,"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire " wrote: Hi Any help? I have this formula in E8, f8,¦to x8 sheet2: =IF(OR(ISNUMBER('sheet1'!E8);ISTEXT(' sheet1'!E8));' sheet1'!E8;"") In AH8 I have this formulae, as array formulae: =IF(E8<50;$E$7;"")&" "&IF(F8<50;$F$7;"")&" "&IF(G8<50;$G$7;"")&" "&IF (H8<60;$H$7;"")&" "&IF(I8<60;$I$7;"")&" "&IF(J8<60;$J$7;"")&" "&IF (K8<60;$K$7;"")&" "&IF(R8<50;$R$7;"")&" "&IF(S8<50;$S$7;"")&" "&IF (T8<50;$T$7;"")&" "&IF(U8<60;$U$7;"")&" "&IF(V8<60;$V$7;"")&" "&IF (W8<60;$W$7;"")&" "&IF(X8<60;$X$7;"") Which checks the score and gives the name of the subject if it does not meet the if check It works perfectly when there is only numbers, but sometimes I have instead of a score (number), a text such as (absent), and I want to return the name of the subject like ($E$7). In E8 could be a number or a text, or it can be empty the result of the first formula, so I tried this =IF(OR(E8<50;ISTEXT(E8));$E$7;"")&" &IF(OR(F8<50;ISTEXT(F8));$F$7;"") &" "&IF(OR(G8<50;ISTEXT(G8));$G$7;"")&" "&IF(OR(H8<50;ISTEXT(H8));$H $7;"")&" "&IF(OR(I8<50;ISTEXT(I8));$I$7;"")&" "&IF(OR(J8<50;ISTEXT (J8));$J$7;"")&" "&IF(OR(K8<50;ISTEXT(K8));$K$7;"")&" "&IF(OR (R8<50;ISTEXT(R8));$R$7;"")&" "&IF(OR(S8<50;ISTEXT(S8));$S$7;"")&" "&IF (OR(T8<50;ISTEXT(T8));$T$7;"")&" "&IF(OR(U8<50;ISTEXT(U8));$U$7;"")&" "&IF(OR(V8<50;ISTEXT(V8));$V$7;"")&" "&IF(OR(W8<50;ISTEXT(W8));$W$7;"") &" "&IF(OR(X8<50;ISTEXT(X8));$X$7;"") But it gives all the names of the subjects even if it is empty since the formulae is giving which is text, what can I do to make it work with numbers, text or . Thanks in advance Jam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |