HELP with IF formula and Circular Reference

DarwinResearchGroup

Hello,

 

Here's the set up:

 

I have Four columns I'm working with:

Progress (Drop down menu) [the main option i'm working with is "to do"]

Duration (Duration) [This column is based on how long we have to complete the project]

H# (Number) [This is just a column that takes duration out of days to just numbers]

Allocation % (Allocation) [How much of the work is Allocated to each person]

 

So basically what I want is when I change the progress column to "To do", I want the allocation % column to display a percentage. The percentage will be 16/(duration)*8

 

Here's the formula I input: =IF(Progress1 = "To Do", [Allocation %]1 = "16/([H#]18 * 8)")

^^ I've tried many variations of this formula, and nothing works.

 

Please advise!!

Nic Larsen

I think this will work in your Allocation% column:

IF(Progress@row = "To Do", 16 / [H#]@row* 8)

DarwinResearchGroup

Thanks so much Nic!

 

This is almost perfect. Here's the formula I'm using now: 

=IF(Progress1 = "For First Edit", (4 / ([H#]1 * 8)), IF(Progress1 = "For John To Edit", (4 / ([H#]1 * 8)), IF(Progress1 = "To Do", (16 / ([H#]1 * 8)), IF(Progress1) = "In Progress", (16 / ([H#]1 * 8)), IF(Progress1 = "For Final Edit", 1 / ([H#]1 * 8)))))

 

Sorry that I can't post it with colors. 

 

So "For First Edit" works

"For John To Edit" works

but "To Do" "In Progress" and "For Final Edit" all give me Incorrect Argument Set.

 

Any advice on that part?

Paul Newcome

I almost feel like this could be approached another way that may make getting this to work a little bit easier. It seems like all of your IF statements include [H#] * 8 with the different criteria determining the value that is divided by that.

 

One way to approach this which will help knock out some of those pesky parenthesis is to write your IF statements out to just display the VALUE.

 

IF(Progress1 = "For First Edit", 4)

IF(Progress1 = "For John To Edit", 4)

IF(Progress1 = "To Do", 16)

IF(Progress1) = "In Progress", 16)

IF(Progress1 = "For Final Edit", 1)

.

We see that two options can produce a 4 and two options can produce a 16, so lets consolidate there with OR statements.

 

IF(OR(Progress1 = "For First Edit", Progress1 = "For John To Edit"), 4)

IF(OR(Progress1 = "To Do", Progress1 = "In Progress"), 16)

IF(Progress1 = "For Final Edit", 1)

.

Now we write out our nested IF to generate either 4, 16, or 1 depending on the Progress.

 

=IF(OR(Progress1 = "For First Edit", Progress1 = "For John To Edit"), 4, IF(OR(Progress1 = "To Do", Progress1 = "In Progress"), 16, IF(Progress1 = "For Final Edit", 1)))

.

Then we can divide that result by ([H#]1 * 8).

=IF(OR(Progress1 = "For First Edit", Progress1 = "For John To Edit"), 4, IF(OR(Progress1 = "To Do", Progress1 = "In Progress"), 16, IF(Progress1 = "For Final Edit", 1))) / ([H#]1 * 8)

.

See if this works the kinks out for you.