If function and math formulas
I need help with the if fuction math formulas for the following situation in
my spread sheet: I have two rows of values. Values in row 1 correspond to the values in row 2. I need to count the number of 0 in row 1 and average the corresponding values in row 2*3+3. I try the formula: =IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3, but it did not work. Please help. Thank you, Linda |
If function and math formulas
2*3+3 = 9 for me. So
=sumif(A1:Z1,0,A9:Z9)/Countif(A1:Z1,0) -- Regards, Tom Ogilvy "Linda" wrote: I need help with the if fuction math formulas for the following situation in my spread sheet: I have two rows of values. Values in row 1 correspond to the values in row 2. I need to count the number of 0 in row 1 and average the corresponding values in row 2*3+3. I try the formula: =IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3, but it did not work. Please help. Thank you, Linda |
If function and math formulas
=IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3,
The countif will count the number of Zero's, the the If will try and look for a true/false statement with that number, so it will fall over. If you had in row A3 =IF(A1=0,A2,"") and drag across it will only show the values you want to average, then you could do =AVERAGE(A3:Z3)*3+3 and any values that are "" will be ommited *not* counted as 0. "Linda" wrote: I need help with the if fuction math formulas for the following situation in my spread sheet: I have two rows of values. Values in row 1 correspond to the values in row 2. I need to count the number of 0 in row 1 and average the corresponding values in row 2*3+3. I try the formula: =IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3, but it did not work. Please help. Thank you, Linda |
If function and math formulas
I see the 2 was refering to row 2, so try this instead:
Assumes for each value in row 2 that has a zero in row 1, you want to multiply it by 3 and add 3 to it. then average all the results. =(sumif(A1:Z1,0,A2:Z2)*3+3*Countif(A1:Z1,0))/Countif(A1:Z1,0) -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: 2*3+3 = 9 for me. So =sumif(A1:Z1,0,A9:Z9)/Countif(A1:Z1,0) -- Regards, Tom Ogilvy "Linda" wrote: I need help with the if fuction math formulas for the following situation in my spread sheet: I have two rows of values. Values in row 1 correspond to the values in row 2. I need to count the number of 0 in row 1 and average the corresponding values in row 2*3+3. I try the formula: =IF(COUNTIF(A1:Z1,0),AVERAGE(A2:Z2)*3+3, but it did not work. Please help. Thank you, Linda |
All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com