Nested IF function error "You've entered too many arguments..."

[lhendrickson]

Hi there. I am getting frustrated. I am trying to created a nested IF formula in an Excel gantt chart to calculate the percentage complete of a subtask. I tried each "IF" separately and they work the way I want them to, but when I try to nest them I get the message "You've entered too many arguments for this function." Can anyone spot what I'm doing wrong?

This is the formula I am trying to enter:

=IF(G8>G14,(G8-G14)/I14,0,IF(G8>H14,1,G8-G14/I14))


Thanks for your help!
VA Construction Schedule IF.xlsx

[rvasquez]

It's just like the error says you've entered too many arguments.

After the first G14 and the first comma should be your what you want to return if G8 is greater than G14.

After the second comma you have a zero which would end the the function as this is what you are defining as what the cell should display if G8 is not greater than G14.

I think for what you are trying to accomplish you should just remove the second comma and the 0 following it.

Which in total the function would say if G8 > G14 then (G8-G14)/I14, if G8 <G14 then if G8>H14 then one, if G8<G14 then G8-G14/I14.

[lhendrickson]

Thanks for your help...
Removing the second , and the 0 DOES make it a working formula. However, it does not do what I am looking for.

What I need is for this:

Today's Date = G8
Subtask Start Date = G14
Subtask End Date = H14
Work Days = I14
% Complete = J14

J14 (% Complete) is the cell that needs the formula.
If G8 (Today's Date)is less than G14 (Start Date), than J14 should equal 0%.
If G8 (Today's Date) is greater than H14 (End Date), than J14 should equal 100%.
If G8 (Today's Date) falls somewhere in between G14 (Start Date) and H14 (End Date), than J14 should equal H14 (End Date) minus G8 (Today's Date) divided by I14 (Work Days).
This should give me what percent completion the subtask is at based on what day it is.

Thanks again!

[shg]

If you're counting all days between, maybe

=MAX(0, MIN(1, (G8-G14) / (H14-G14)))

[lhendrickson]

Nope, that didn't do it either.

Thanks, though!

[shg]

For example, ...?

[lhendrickson]

I'm sorry? What are you asking for?

[vlady]

hello
maybe?

=IF(G8<G14,0%,IF(G8>H14,100%,IF(AND(G8>G14,G8<H14),(H14-G8)/I14)))

[lhendrickson]

OOOOH! That's almost it, vlady! The only issue is if "today's date" falls between the "start" and "end" dates, it calculates the percentage backwards, meaning that the later the date, the smaller the percent complete. If "today's date" falls outside that range (either before or after) it calculates correctly, though.

[lhendrickson]

OK, I changed it to the following and that cleared up the backwards percentage.

=IF(G8<G14,0%,IF(G8>H14,100%,IF(AND(G8>G14,G8<H14),(G8-G14)/I14)))

The only remaining issue is that if "today's date" equals either the "start" or "end" date, it returns "FALSE". How can I get it to accurately return 0% or 100%?

Thanks everyone for helping me through this! If there's a will, there's a way!

[lhendrickson]

Actually, that last sentence is mistaken. If "today's date" equals the "start" date, it should return 20% and if it equals the "end" date, it should equal 80%, not 0% and 100% like I stated in my previous post. Sorry for the confusion. Anything before the "start" should equal 0% and anything after "end" should equal 100%.

[vlady]

=if(g8=g14,20%,if(g8=h14,80%,if(g8<g14,0%,if(g8>h14,100%,(h14-g8)/i14))))

[lhendrickson]

You nailed it! Thank you SO much vlady!!!

[kisho20]

Hi, Try as below

=IF(G8>G14,((G8-G14)/I14),IF((G8>H14),1,((G8-G14)/I14)))

[provangelist]

Hello,
I have also a problem with the nested IF formula. Seems like I made it correct byt get the error, that have entered many arguments.

The task is as follows.

On one hand, I have the count of sold items. From 0 to more than 50 000 item.
From the other hand, with the increase of the count, the price drops down.

A20 1000 0.90 B20
A21 2000 0.80 B21
A22 5000 0.70 B22
A23 10000 0.60 B23
A24 20000 0.50 B24
A25 30000 0.40 B25
A26 50000 0.30 B26


And the cell where the end result should be illustrated is B13

I put the following formula:

=IF(B13<A21,B13*B20,(IF(B13>A21<A22,B13*B21),(IF(B13>A22<A23,B13*B22),(IF(B13>A23<A24,B13*B23),(IF(B13>A24<A25,B13*B24),(IF(B13>A25<A26,B13*B25),(IF(B13>A26,B13*B26,"0"))))))))

I checked the number of parentheses, it is ok.

Do you know what might be the problem?

Thanks

[jeffreybrown]

Unfortunately your post does not comply with Rule 4 of our Forum RULES.

Please do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.