Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Math formulas possible with overstrike characters? | Excel Discussion (Misc queries) | |||
Math Function? | Excel Discussion (Misc queries) | |||
Need help with math Function | Excel Worksheet Functions | |||
Excel Math formulas | Excel Discussion (Misc queries) | |||
need list of free math formulas in excel | Excel Worksheet Functions |