Computer Shopper 2019-11-01

HELPFILE&BUSINESSHELP

ISSUE 381|COMPUTERSHOPPER|NOVEMBER 2019 121

Irecently bought aSynology DS218j NAS
device,which works brilliantly apart from
one detail. I’ve used Acronis True Image to
schedule aweekly backup of my primary
PC, which contains my entire work and
personal archive.The backup size is around
500GB. Istart anew full backup set every
month, followed by three incremental
backups before beginning anew set.
The PC and NAS are wired to aGigabit
switch, so backups are typically fast: even
afull backup usually only takes two or
three hours. However,acouple of times
now I’ve noticed things taking farlonger
than theyshould, and on investigation I’ve
discovered that the NAS’s network
interface has dropped down to 100Mbit/s.
Can you suggest why this is happening, and
how Iprevent slow backups?
Dean Harris

Ethernet interfaces usually auto-negotiate
with each other to agree the fastest speed and
duplex mode theyboth support. Provided you
have agood-quality Gigabit switch, the DS218j
should be operating at 1,000Mbit/s with full
duplex, meaning data can be sent and received
at the same time.Reboot the NAS to reset
the interface,then check the network status

Gettingmybackup

from Control Panel in

DiskStation Manager: select

Network, click the Network

Interface tab then expand

the LAN entry.

Assuming this confirms

1,000Mbit/s and full duplex,

the most likely problem is a

poor-quality cable between

the NAS and switch.

Autonegotiation doesn’t test

the quality of alink between

two interfaces, so it’s not

uncommon foratwisted or cheap cable to

cause Gigabit Ethernet to fail once the link

becomes busy.Asageneral rule,you should

use either aCat5e or Cat6 cable that’s no

longer than necessary,and avoid either

twisting it or subjecting it to sharp bends. In

theory,the cables’ twisted pair design helps

shield it from interference,but it’s agood idea

to routenetwork cables awayfrom potential

sources such as the NAS’s own power supply.

Unfortunately,there’s not much else you

can do if this fails. While it is possible to force

the DS218j to 1,000Mbit/s with full duplex,

we wouldn’t recommend it: the option isn’t

available via DiskStation Manager,and it

could cause the link to fail altogether.

One solution you might consider is to

createtwo power schedule events to shut

down the NAS then restart it shortly

afterwards, indirectly resetting the network

speed. From Control Panel in DiskStation

Manager,click Hardware &Power,select the

Power Schedule tab,then createashutdown

event for, say, 20 minutes before the backup is

scheduled. Next, createastartup event forfive

or 10 minutes after the shutdown. Ensure both

are ticked to enable them, then return to the

General tab and click Apply.This should

ensure that just before the backup the link is

returned to 1,000Mbit/s if necessary,but it

won’t help if the issue is being caused by

heavy network traffic during the backup itself.

Some time ago Iwrotetoyou seeking

help with aspreadsheet to calculate

the best scores forour golf society.

The challenge was to obtain each

player’s 15 best scores forthe year,

allowing amaximum of five scores

from rounds played on Mondays.

Although KayEwbank seemingly

managed to solve the challenge (see

BusinessHelp,Shopper372), Ican’t get

her solution to work.

I’ve created aspreadsheet where the

first workbook just lists our players, and

the second lists the dates on which

they’ve played and the score they

achieved. The magic is meant to happen

in the third workbook, where I’ve

successfully sorted the data from the

second book to remove blank scores.

Kaysuggested Iuse acombination of

Excel’s large function, if statements and

arrayformulae to obtain the best five

scores obtained on Mondays, and the

best 15 scores obtained on other days,

then do so again on this range of 20

results to find the top15overall scores.

It’s here that I’ve hit trouble.

The formula =LARGE(IF(Player=I3,IF

(playday="Mon”,Score)),1) is meant to

find the best Mondayscore forthe

player in cell I3, but in my spreadsheet

it’s just returning azero forall players.

Below-parspreadsheet?

I’m sure there’s asimple solution, but I

can’t see what it is. Can you?

Martin Simpson

This was atrickyproblem and, as you say,

Kay’s solution relies on arrayformulae.Asshe

noted, to make aformula work as an array

formula in Excel, you need to enter it with

Ctrl-Shift-Enter.Checking your spreadsheet, it

appears that you had missed this part of the

instructions; editing the relevant cells and

re-entering them

as arrayformulae

immediately got

them to display

the correct result.

We noted that

your current

spreadsheet only

calculates the 10

best non-Monday

scores, rather than

the 15 you need

to ensure you end

up with aplayers’s

15 best scores

overall. As it

stands, aplayer’s

best 15 scores will

always include

the five Monday

and 10 non-

Mondayscores

you’re calculating, rather than abest 15

obtained from up to five Mondayscores

and up to 15 non-Mondayscores.

One further point is that the spreadsheet

you provided contained only asmall portion

of the year’s data, so many of the topscore

fields contain the #NUM error.These will

disappear once the second workbook has

data covering at least five Mondays and

15 other dates.

⬆It’s not ideal, but you can reset the network interface by scheduling the

NAS off and on again

⬆Don’t panic! This faceful of #NUM errors is simply down to alack of source data