quite possibly a confusing excel question.

[Hot Spenny]

ok, this is for a friend, and i can't quite get it right. it might also be a touch tedious to explain, so bare with me!

so he has a value of 100 in a cell, lets say B5. he then has a number of other cells, lets say E5 to P5, all with the value 0.

He wants to make it so that, whenever a cell from E55 has a one in it, that B5 minuses 1. so if E5, F5, G5 were given the value 1, B5 will become 97.

I tried an if statement. i am by no means a computer person, but managed to get it working for one cell, but can't quite make it account for all the other cells.

I hope it's not too confusing, hopefully someone knows what I am saying!

 

[DamianBis]

Hmmm. i think i know it. give me 2 minutes. i'll boot up excel to try it

=100 - SUM(E55)

If you put 2 in. it will just mess the whole thing up and take 2 from it.

 

[Hot Spenny]

yeah thanks Dam, i tried to use an if statement, something like =if(E5>0, B5-1, B5) only thing is i can't apply it to the cell b5, have to put it in another cell, and can't get it to account for all of the other columns.

 

[The Passenger]

you need a sumif statement

for instance, in b3 you have 100, in d3 to d12 you have a series of values,

the following statement in b4:
=SUMIF(D3: D12,"1",D3: D12)

this will give you the sum of all numbers in d3 to d12 with the value of 1

the following statement in b4:
=SUMIF(D3: D12,">1",D3: D12)

this will give you the sum of all numbers in d3 to d12 with the value of more than 1

you would then just have =(b3-b4) in another cell

you can play around with a few things to try nad get it into one cell but i can't be assed now, its too late. but that is a start

 

[Falchoon]

worst case scenario even with no excel knowledge couldn't you just type

+b5-c5-d5-e5-f5-g5-h5-i5-j5-k5-l5-m5-n5-o5-p5

or if it only positively absolutely can minus a 1 and not say a 2

long version

=100-IF(C5=1,1)-IF(D5=1,1)-IF(E5=1,1)-IF(F5=1,1)-IF(G5=1,1)-IF(H5=1,1)-IF(I5=1,1)-IF(J5=1,1)-IF(K5=1,1)-IF(L5=1,1)-IF(M5=1,1)-IF(N5=1,1)-IF(O5=1,1)-IF(P5=1,1)

short version

=100-SUMIF(C55,1,C55)