Formulas based on a set value

[Broo2439]

Hey all
I'm sorry if this question has been asked before, but I'm trying to sort a mega spreadsheet to make something at work much easier and simplier

Basically every day, someone has to give me a number which I then add to the spread sheet.
If this number is '4' I would like the value to be saved elsewhere as 0
If its less then '4' I would like the amount its less by to be saved so -1 if its 3, -2 if its 2 etc
If the value is over '4' I would like it to say how much it is over. so a 1 if the value is 5, a 2 if the value is 6

And then I would like all of those values to be added together so I can see by how much we are over or under target.

I guess the easier way to explain it with a mock table

Value =4
[TABLE="width: 500"]
<tbody>[TR]
[TD]25/6/18
[/TD]
[TD]26/6/18
[/TD]
[TD]27/6/18
[/TD]
[TD]28/6/18
[/TD]
[TD]29/6/18
[/TD]
[TD]30/6/18
[/TD]
[TD]1/7/18
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]=0
[/TD]
[TD]=-1
[/TD]
[TD]=0
[/TD]
[TD]=-3
[/TD]
[TD]=2
[/TD]
[TD]=-2
[/TD]
[TD]=-3
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]-7
[/TD]
[/TR]
</tbody>[/TABLE]

This will continue over many weeks, but I want the total value to stay at the top of the page, and adds up all the weeks.

I hope I've explained this well enough and someone will be able to help me out.

Thanks in advance

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this does what you need

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Value​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Result​

[/td][td]

-7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

25/06/2018​

[/td][td]

26/06/2018​

[/td][td]

27/06/2018​

[/td][td]

28/06/2018​

[/td][td]

29/06/2018​

[/td][td]

30/06/2018​

[/td][td]

01/07/2018​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

4​

[/td][td]

3​

[/td][td]

4​

[/td][td]

1​

[/td][td]

6​

[/td][td]

2​

[/td][td]

1​

[/td][td][/td][/tr]
[/table]

Formula in B2
=SUMPRODUCT(--(A4:Z4<>""),A4:Z4-B1)

You can increase the range A4:Z4 as needed

Hope this helps

M.

 

[Broo2439]

Hey there!
Thanks for your speedy response.
The only issue I see is the longevity of this formula
I would like to then be able to put in The A5 Line, the next week of dates.
Then the A6 line the next set of values, to be added into the top result.

Sorry for being a pain in the backside

 

[Marcelo Branco]

You should try a different way to organize your data. Or you will have to adjust the ranges every week.

M.

 

[Broo2439]

Is it possible to set the formula so it only grabs values from even lines? And then set it to grab every even line value, and ignore odd line values so I can put the dates in those lines

 

[Marcelo Branco]

Is it possible to set the formula so it only grabs values from even lines? And then set it to grab every even line value, and ignore odd line values so I can put the dates in those lines

Not sure i understand what you trying to do. Try to provide an example.

M.