Conditional formula in excel?

willhub · 12 Apr 2019 at 13:22

Hi,

Trying to do a calculation that basically shows how much over 480 seconds the value of a time brake is, I then will add these up in total over a team, now adding them up is all messed up when you're under the 480 seconds, so I simply want it to show the total over when the figure is over 480 seconds.

Hope that makes sense

Thanks.

Martynt74 · 12 Apr 2019 at 14:09

How is your time break value stored? Is it in a time calculation or just a number? Stored as seconds? minutes?

If you can share a rough example of the data table it'd help

willhub · 12 Apr 2019 at 14:32

I store it as seconds, there is another cell that shows it in minutes, but I use the seconds value formatted in seconds for the actual calculation.

bremen1874 · 12 Apr 2019 at 14:41

You could do something like

=(MAX(0,A1-480))

If the number of seconds <= 480 it'll return 0, otherwise it'll return the seconds over 480.

Greboth · 12 Apr 2019 at 15:39

Do you need the individual values or only per team? Bremens works if you need the individual which you could then sum per team.

If you only need the totals you could go with;

=SUMIF(A1:A5,">"&480,A1:A5)

Where A1:A5 is the range of individual cells for the time brake of a team.

Edit: Just re-reading the OP, you said the total over 480 so presume you don't want the 480 included. So you could go with;

=SUMIF(A1:A5,">"&480,A1:A5)-COUNTIF(A1:A5,">"&480)*480

Double07 · 12 Apr 2019 at 16:39

willhub said:
Hi,

Trying to do a calculation that basically shows how much over 480 seconds the value of a time brake is, I then will add these up in total over a team, now adding them up is all messed up when you're under the 480 seconds, so I simply want it to show the total over when the figure is over 480 seconds.

Hope that makes sense

Thanks.

Imagine if someone who you are spying on at work came on here saw this, oh the fun they could have! Timing peoples p**s breaks, and then people actually helping you. Boke!

willhub · 12 Apr 2019 at 17:06

To be fair I'm just doing it as instructed, the stats are out there already I'm just been creative and improvising the way I send the stats. Every little helps out bonus and I want money.