Excel formula question

  hastelloy 12:52 09 Nov 2007
Locked
Answered

I have a series of sheets, which follow on from one another. In the 1st sheet N39=N37-N38 and N40=N38-N37. Obviously 1 of these will always be positive and the other negative unless both = 0.

The question is, how do I make Sheet2 E9=Sheet1 N39 if N39 is positive but otherwise 0 and sheet 2 E10 = sheet 1 N40 if N40 is positive but otherwise 0.

Thanks for any help - I've been playing with this for hours and am either missing something very simple or its way over my head (probably the latter).

  PalaeoBill 13:04 09 Nov 2007

make Sheet2 E9=IF(sheet1!n39>0,sheet1!n39,0)
and
Sheet2 E10=IF(sheet1!n40>0,sheet1!n40,0)

  Taff™ 13:06 09 Nov 2007

Something Like =IF(Sheet1!N40>0,Sheet1!N40,"0") in Cell E10 and =IF(Sheet1!N39>0,Sheet1!N39,"0") in E9 I think.

  PalaeoBill 13:07 09 Nov 2007

You may also wish to investigate the absolute function ABS(). ABS(N37-N38) will give you the difference between the two values, stripping any negative sign.

  silverous 17:18 09 Nov 2007

you could also do =MAX(Sheet1!N40,0) I believe.

  hastelloy 17:51 09 Nov 2007

It seems that the problem isn't quite what I thought it was!! In asking my question, I called the sheets 1 and 2 for simplicity and when they are called this it works. Unfortunately, I need them to be "Week 59-53 (07)" for sheet 1 and "Week 01-04 (08)" for sheet 2 etc. If I use the original (ie sheet1, 2 etc) names for sheets it works fine and seems to carry on working if I rename the sheets. Unfortunately it doesn't seem to work if the sheets start off being named as I want them. I haven't had time to really check this out but will have another go on Monday

  VoG II 17:55 09 Nov 2007
Answer

You need to wrap the sheet names in single quotes like

'Week 59-53 (07'!N40

  hastelloy 18:11 09 Nov 2007

I'll give that a try on Monday.

Have a good weekend.

  hastelloy 15:46 13 Nov 2007

The formulae given above and Vog's single quotes solved the problems. Thanks again to all.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

ManvsMachine win UK's only Gold Design Cannes Lions award

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?