ABS/IF formula
 

Hi,

i have a sheet, this follows the day of the week across the row and a
selection of devices going down the column:

mon tue wed thur
device1 10 11 15 18
device2 20 22 30 32
device3 22 25 26 28

on another sheet i need to work out the difference in time that the device
has been used, so for device1 between monday and tuesday it has been used for
one hour.

for this i have been using the =ABS formula, the problem comes when on some
days the device could be in for repair. if this is the case i would like the
formula to understand the cell content as 0 so as far as the formula is
concerned the cell value is 0 (zero) eventhough its actual value is 'repair'

my problem is, is that i cant sufficiently nest the required logical tests
correctly and it has become very annoying, if anyone could halp it would be
verty much apprieciated.

cheers

The Noob.

ABS/IF formula
 

Could you post the formula(s) you have tried, so that we can work out
your cell references etc.

Pete

On Dec 8, 1:31*pm, Noob McKnownowt
wrote:
Hi,

i have a sheet, this follows the day of the week across the row and a
selection of devices going down the column:

* * * * * * mon *tue *wed *thur
device1 * 10 * *11 * *15 * *18 * * *
device2 * 20 * *22 * *30 * *32
device3 * 22 * *25 * *26 * *28

on another sheet i need to work out the difference in time that the device
has been used, so for device1 between monday and tuesday it has been used for
one hour.

for this i have been using the =ABS formula, the problem comes when on some
days the device could be in for repair. if this is the case i would like the
formula to understand the cell content as 0 so as far as the formula is
concerned the cell value is 0 (zero) eventhough its actual value is 'repair'

my problem is, is that i cant sufficiently nest the required logical tests
correctly and it has become very annoying, if anyone could halp it would be
verty much apprieciated.

cheers

The Noob.

ABS/IF formula
 

the best i have done so far is:

=ABS('07.12.08'!B11-'07.12.08'!D11)

but the references arent really that important i can do that, i am just
struggling to understand how to perform the tests correctly.

=ABS(IF(OR('07.12.08'!B11="REPAIR",
'07.12.08'!D11="REPAIR"),0,"")'07.12.08'!B11- '07.12.08'!D11)))

obviously it dosent work :)

cheers

The Noob.

"Pete_UK" wrote:

Could you post the formula(s) you have tried, so that we can work out
your cell references etc.

Pete

On Dec 8, 1:31 pm, Noob McKnownowt
wrote:
Hi,

i have a sheet, this follows the day of the week across the row and a
selection of devices going down the column:

mon tue wed thur
device1 10 11 15 18
device2 20 22 30 32
device3 22 25 26 28

on another sheet i need to work out the difference in time that the device
has been used, so for device1 between monday and tuesday it has been used for
one hour.

for this i have been using the =ABS formula, the problem comes when on some
days the device could be in for repair. if this is the case i would like the
formula to understand the cell content as 0 so as far as the formula is
concerned the cell value is 0 (zero) eventhough its actual value is 'repair'

my problem is, is that i cant sufficiently nest the required logical tests
correctly and it has become very annoying, if anyone could halp it would be
verty much apprieciated.

cheers

The Noob.

ABS/IF formula
 

is this:

=ABS(IF(AND(C7<"repair",B7<"repair"),C7-B7,))

what you need?

ABS/IF formula
 

First thing to remember when you are nesting functions is that pairs of
parentheses need to match. You've got 3 opening and 5 closing. Think again
about which parameters you are trying to provide to which function, and
arange your parentheses accordingly.

It isn't entirely clear what you are trying to achive, but perhaps:
=ABS(IF(OR('07.12.08'!B11="REPAIR",
'07.12.08'!D11="REPAIR"),0,'07.12.08'!B11- '07.12.08'!D11))
--
David Biddulph

"Noob McKnownowt" wrote in
message ...
the best i have done so far is:

=ABS('07.12.08'!B11-'07.12.08'!D11)

but the references arent really that important i can do that, i am just
struggling to understand how to perform the tests correctly.

=ABS(IF(OR('07.12.08'!B11="REPAIR",
'07.12.08'!D11="REPAIR"),0,"")'07.12.08'!B11- '07.12.08'!D11)))

obviously it dosent work :)

cheers

The Noob.

"Pete_UK" wrote:

Could you post the formula(s) you have tried, so that we can work out
your cell references etc.

Pete

On Dec 8, 1:31 pm, Noob McKnownowt
wrote:
Hi,

i have a sheet, this follows the day of the week across the row and a
selection of devices going down the column:

mon tue wed thur
device1 10 11 15 18
device2 20 22 30 32
device3 22 25 26 28

on another sheet i need to work out the difference in time that the
device
has been used, so for device1 between monday and tuesday it has been
used for
one hour.

for this i have been using the =ABS formula, the problem comes when on
some
days the device could be in for repair. if this is the case i would
like the
formula to understand the cell content as 0 so as far as the formula is
concerned the cell value is 0 (zero) eventhough its actual value is
'repair'

my problem is, is that i cant sufficiently nest the required logical
tests
correctly and it has become very annoying, if anyone could halp it
would be
verty much apprieciated.

cheers

The Noob.

ABS/IF formula
 

This seems to be spot on pal, thank you very much you're a star.

cheers

The Noob.

"Jarek Kujawa" wrote:

is this:

=ABS(IF(AND(C7<"repair",B7<"repair"),C7-B7,))

what you need?

ABS/IF formula
 

welcome, any time
;-)))
could you give some high mark to my post?
;-)))