Advice- make sense of this...!

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Sep 13, 2006
Messages
10
=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

New Member
Joined
Sep 14, 2006
Messages
22
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
=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
 

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top