| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF formula
Sample table below
The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
Try one of these ARRAY FORMULAS*:
F5: =IF(COUNTIF(F1:F4,"N/A")0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) or F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) This one returns N/A if any cell contains text. Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
Maybe this will help.........
=IF(ISNA(SUMPRODUCT(F1:F4,$A$1:$A$4)),"",SUMPRODUC T(F1:F4,$A$1:$A$4)) Vaya con Dios, Chuck, CABGx3 "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
In column b through e there should be a number, then in column f there should
be a N/A. If there is a N/A in the column N/A, if there are only numbers then multiply that column times column A "CLR" wrote: Maybe this will help......... =IF(ISNA(SUMPRODUCT(F1:F4,$A$1:$A$4)),"",SUMPRODUC T(F1:F4,$A$1:$A$4)) Vaya con Dios, Chuck, CABGx3 "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
Ron,
I need a sum of that column times column A, if in that column there is not a N/A. I think the count will count how many not give me the total I need. Right?? "Ron Coderre" wrote: Try one of these ARRAY FORMULAS*: F5: =IF(COUNTIF(F1:F4,"N/A")0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) or F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) This one returns N/A if any cell contains text. Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
Regarding:
F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) That fomula counts the numeric cells in F1:F4. If that count is NOT 4, then there is at least one text cell...so return: N/A Otherwise, multiply each Col_F value by its corresponding Col_A value and sum the products. Am I missing something? Can an you give an example? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Ron, I need a sum of that column times column A, if in that column there is not a N/A. I think the count will count how many not give me the total I need. Right?? "Ron Coderre" wrote: Try one of these ARRAY FORMULAS*: F5: =IF(COUNTIF(F1:F4,"N/A")0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) or F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) This one returns N/A if any cell contains text. Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
The first formulas did work. Sorry for the mis understanding.
"Ron Coderre" wrote: Regarding: F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) That fomula counts the numeric cells in F1:F4. If that count is NOT 4, then there is at least one text cell...so return: N/A Otherwise, multiply each Col_F value by its corresponding Col_A value and sum the products. Am I missing something? Can an you give an example? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Ron, I need a sum of that column times column A, if in that column there is not a N/A. I think the count will count how many not give me the total I need. Right?? "Ron Coderre" wrote: Try one of these ARRAY FORMULAS*: F5: =IF(COUNTIF(F1:F4,"N/A")0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) or F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) This one returns N/A if any cell contains text. Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
What is wrong with this IF formula
Sorry, I miss understood! This worked great.
Thank you. "Ron Coderre" wrote: Try one of these ARRAY FORMULAS*: F5: =IF(COUNTIF(F1:F4,"N/A")0,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) or F5: =IF(COUNT(F1:F4)<4,"N/A",SUMPRODUCT(F1:F4,$A$1:$A$4)) This one returns N/A if any cell contains text. Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "sweetsue516" wrote: Sample table below The formula will be in cell f5 =IF(f1:f4)="N/A","N/A",SUMPRODUCT(f1:f4,$A1:A4)) If cells f1 through f4 equals "N/A" (which is text I put in there) then in cell f5 show N/A, but if f1 through f4 equal all numbers then equal f1:f4 mulitiplied by a1:a4. I will be using this formula in b5, c5,d5, e5. The actual table is bigger and the numbers will change. A B C D E F 1 10% 15.58 38.50 26.68 9.43 N/A 2 10% 13.51 36.03 24.85 6.41 3.34 3 4% 8.41 28.66 20.26 4.86 N/A 4 4% 3.67 1. 36 15.75 9.89 9.2 5 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula retrieves wrong data | Excel Discussion (Misc queries) | |||
| Recalculating Formula - Getting the wrong answers | Excel Discussion (Misc queries) | |||
| Wrong answer after using the payment formula in excel | New Users to Excel | |||
| Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
Linear Mode
