Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead of V
Hi
in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead of V
You can build =ISNUMBER() into your formula.
But that would mean having it in there 8 times. It is workable but =(IF(ISNUMBER(C8),C8,0)*IF(ISNUMBER(C$6),C$6,0)+ ... quite a long formula. How about having another row (9) with this =IF(ISNUMBER(C8),C8,0) etc Then do your working out against this row instead. Or you can write out the whole isnumber piece if you can be bothered. -- Allllen "Gilles St-Amour" wrote: Hi in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead of V
I think that this is what =sumproduct() was intended for:
=SUMPRODUCT(C$6:F$6,C8:F8) Gilles St-Amour wrote: Hi in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead of V
This is kinda brute force, but should do the job..........
=IF(ISNUMBER(C8),C8*C$6,0)+IF(ISNUMBER(D8),D8*D$6, 0)+IF(ISNUMBER(E8),E8*E$6,0)+IF(ISNUMBER(F8),F8*F$ 6,0) All on one line, watch out for word-wrap when copying.......... hth Vaya con Dios, Chuck, CABGx3 "Gilles St-Amour" wrote: Hi in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead
hi
that did the job Thank you very much "CLR" wrote: This is kinda brute force, but should do the job.......... =IF(ISNUMBER(C8),C8*C$6,0)+IF(ISNUMBER(D8),D8*D$6, 0)+IF(ISNUMBER(E8),E8*E$6,0)+IF(ISNUMBER(F8),F8*F$ 6,0) All on one line, watch out for word-wrap when copying.......... hth Vaya con Dios, Chuck, CABGx3 "Gilles St-Amour" wrote: Hi in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
disregard a cell on occasion when there is text in it instead
You're very welcome.........and thanks for the feedback.
Vaya con Dios, Chuck, CABGx3 "Gilles St-Amour" wrote: hi that did the job Thank you very much "CLR" wrote: This is kinda brute force, but should do the job.......... =IF(ISNUMBER(C8),C8*C$6,0)+IF(ISNUMBER(D8),D8*D$6, 0)+IF(ISNUMBER(E8),E8*E$6,0)+IF(ISNUMBER(F8),F8*F$ 6,0) All on one line, watch out for word-wrap when copying.......... hth Vaya con Dios, Chuck, CABGx3 "Gilles St-Amour" wrote: Hi in the following simple formula =(C8*C$6)+(D8*$D$6)+(E8*$E$6)+(F8*$F$6) sometime the value in the cell c8 or d8 or e8 or f8 is actually a letter( because of a special requirement), of course I get en error when that happen. I do not want to remove the cell from the formula everytime because I will input number sometime in that cell. I looking to keep the same formulas but with something to disregard the text value when required or take the number value when there is one in the cell. So that way I can copy the formula whitout having the worry about editing everytime Thanks in advanced Gilles |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aligning Wraped Text to the bottom of a cell | Excel Discussion (Misc queries) | |||
linking a cell containing text to another cell containing text / data | Excel Discussion (Misc queries) | |||
Text not continuing to wrap for large block of text in Excel cell | Charts and Charting in Excel | |||
Cell Capacity - text | Excel Discussion (Misc queries) | |||
How can I make an excel cell equal to the value of a frame object text box | Excel Worksheet Functions |