Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max problem
Hi
In a range of cells i have the formula, =IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'" &(B1)&"'!c5")), this returns N/a when c5 is empty. I have an average of the cells in the range, at the end of the row with the following formula =AVERAGE(IF(ISNUMBER(B3:M3),B3:M3)) All this is working fine, as the N/A is then conditional format in white But when I enter =max(B3:M3), in another cell, I get N/A, presumably, because some cells contain the forced N/A. I have tried =max(isnumber(B3:M3), but still no joy. Any pointers? Thanks in advance Regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max problem
Try
=MAX(IF(ISNUMBER(B3:M3),B3:M3)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "BNT1 via OfficeKB.com" <u19326@uwe wrote in message news:8a964519c38c4@uwe... Hi In a range of cells i have the formula, =IF(INDIRECT("'"&(B1)&"'!c5")="",NA(),INDIRECT("'" &(B1)&"'!c5")), this returns N/a when c5 is empty. I have an average of the cells in the range, at the end of the row with the following formula =AVERAGE(IF(ISNUMBER(B3:M3),B3:M3)) All this is working fine, as the N/A is then conditional format in white But when I enter =max(B3:M3), in another cell, I get N/A, presumably, because some cells contain the forced N/A. I have tried =max(isnumber(B3:M3), but still no joy. Any pointers? Thanks in advance Regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max problem
Thanks for the quick response, works perfect
Regards Peo Sjoblom wrote: Try =MAX(IF(ISNUMBER(B3:M3),B3:M3)) entered with ctrl + shift & enter Hi [quoted text clipped - 16 lines] Regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200809/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |