HELP with IF formula and Circular Reference
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!!
Comments

I think this will work in your Allocation% column:
IF(Progress@row = "To Do", 16 / [H#]@row* 8)

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?

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!