Advice- make sense of this...!

[collinsc]

Can someone help me make sense of the following....

=1-(SUMIF('Project Resource Plan'!$A$3:$A$124,$B27,'Project Resource Plan'!AW$3:AW$124)+SUMIF(Completed!$A$2:$A$373,$B27,Completed!AW$2:AW$373))


I assume the "=1" means if the code is correct then fill "1" in the cell?

what is the dash (-) after the 1 ?

'Project Resource Plan' and 'completed' are other sheets in the workbook.

What does the $ mean?

what is the semicolon represent.?

There are 2 "IFs" do both have to be completed for a "1" to be inserted into the cell?

any advice appreciated.!

Thanks


Chris

 

[onlyadrafter]

Hello,

the dash after the 1 is subtracting the value after the 1 from 1 e.g. 1-8

the $ signs make cell ranges absolute

ie.
if you have A1 as a ref then copy it to A2 it will change to A2. If you copy it to B1 it will change to B1

if you have A$1 as a ref then copy it to A2 it will stay A1. If you copy it to B1 it will change to B$1

if you have $A1 as a ref then copy it to A2 it will change to $A2. If you copy it to B1 it will stay $A1

if you have $A$1 as a ref then copy it to A2 it will stay $A$1. If you copy it to B1 it will stay $A$1

The : indicates a range of cells e.g.

A1:A10 or A1:C1 or A1:F10

As for the two IF's it takes each SUMIF separately. so if one is true, but the other is false it will sum the true part.

 

[speric07]

=1 (1 is the starting value)

- (substiute/take away)

Project Resource Plan (Named Range)

Completed (Named Range)

$ a fixed cell reference (i.e. will stay static and not move)

: says between two data rages (i.e. the table used for the data)

Sumif and If's are different.

Hope this has helped a bit.

Maybe you should search for Sumif on google. As this shold tell you a lot and give you walk throughs.

Richard

 

[sam slade]

Hi,

The dash is a minus symbol. The formula is conditionally summing values in the ranges designated then subtracting the figure from 1.

Any help?

Sam

 

[Andrew Poulsom]

=1 means what it says. The - is a minus sign, so the remainder of the formula is deducted from 1. The colon is the range operator.

SUMIF is a function that takes 3 arguments - the range to evaluate, the criteria, and the range to sum. In:

SUMIF('Project Resource Plan'!$A$3:$A$124,$B27,'Project Resource Plan'!AW$3:AW$124)

The range to evaluate is:

'Project Resource Plan'!$A$3:$A$124

The criteria is:

$B27

and the range to sum is:

'Project Resource Plan'!AW$3:AW$124)

Each cell in the range to evaluate is compared with the criteria and if it matches the correspoding cell in the range to sum is included in the sum.

The $ signs are used to fix the column or row reference when the formula is copied elsewhere.

You may find this link helpful:

http://office.microsoft.com/en-us/assistance/HP052255841033.aspx

 

[collinsc]

Wow, thanks for all the responses. Its been a great help.