Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
I am trying to calculate the difference between two dates and have that
diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
How about
=IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND((C3-D3)/7,5)0.5,ROUND((C3-D3)/7,5),""),"") -- Regards, Peo Sjoblom (No private emails please) "Ted" wrote in message ... I am trying to calculate the difference between two dates and have that diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
On Sun, 20 Nov 2005 17:14:01 -0800, Ted wrote:
I am trying to calculate the difference between two dates and have that diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. See my response (2nd suggestion) in your other thread. It does not return an error if both cells are blank. It is much easier to obtain coherent help if you keep all your related questions in the same thread. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
..
Oops! reverse the division, I just assumed it was a typo -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... How about =IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND((C3-D3)/7,5)0.5,ROUND((C3-D3)/7,5),""),"") -- Regards, Peo Sjoblom (No private emails please) "Ted" wrote in message ... I am trying to calculate the difference between two dates and have that diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
noup, sorry - it gives a blank cell even when the condition it ok (e.g. above
0.5) thanks anyway, Ted. "Peo Sjoblom" wrote: How about =IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND((C3-D3)/7,5)0.5,ROUND((C3-D3)/7,5),""),"") -- Regards, Peo Sjoblom (No private emails please) "Ted" wrote in message ... I am trying to calculate the difference between two dates and have that diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
what means 'blank cell'??
hi, thanks for that - yep that works great $;-D
thanks, Ted. "Peo Sjoblom" wrote: .. Oops! reverse the division, I just assumed it was a typo -- Regards, Peo Sjoblom (No private emails please) "Peo Sjoblom" wrote in message ... How about =IF(AND(C3-D30,COUNT(C3:D3)=2),IF(ROUND((C3-D3)/7,5)0.5,ROUND((C3-D3)/7,5),""),"") -- Regards, Peo Sjoblom (No private emails please) "Ted" wrote in message ... I am trying to calculate the difference between two dates and have that diveded by seven, (e.g. 7/XX-XX) and remove any answers that sum less than 0.5 So far I have the below formula, but it returns an "ERROR" where there is blank cells. I think that is because of the zero in the ...C3-D3<0),.. section =IF(AND(ISNUMBER(C3),ISNUMBER(D3),C3-D3<0),IF(ROUND(7/(C3-D3),5)0.5,ROUND(7/(C3-D3),5),"")) has anyone any ideas please?? Ted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a blank cell | Excel Worksheet Functions | |||
Copying a Blank Cell | Excel Worksheet Functions | |||
Setting Purely BLANK Cell | Excel Worksheet Functions | |||
Replace null string with blank cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |