I am still at it...hour #14 today alone. Please help (again) - This formula is in cell AV =IF(N662="","",IF(N662=100,(Q663,Y663,AG663),IF(Q663+Y663+AG663<=99.99,Q663+Y663+AG663)))

What I am trying to accomplish is this

IFN662 is blank (remain blank)IFN662 is =100 then add Q663,Y663,AG663 (please note if N662 has the value 100 in the cell the value of 100 indicates that there is data inputted across the row, if blank then there isnt and should be skipped)IFthe sum of Q663,Y663,AG663 is 99.99 or less (what I really need is if between 0.1-99.99) then add them, >=100 then s/b 100If N662 is blank, its imporant this cell (which is AV should remain blank also)

Further explanation: The info I am trying to get in cell AV662 is if N662 is blank then AV662 should remain blank, if there is a 100 value in N662 then I need Excel to add up Q663,Y663,AG663. If the answer is equal to or greater than 100 then default to 100, if <100 then sum Q663,Y663,AG663

I hope I explainned this correctly.

TY!

✔ Best Answer

You've added one criteria, but still left out a possibility. You added the "if the SUM is 0, then return 100" but you haven't told us what to return if

the SUM is negative. If that is not a possibility, then this formula should work:=IF(N662="","",IF(N662=100,IF(AND(SUM(Q662,Y662,AG662)>0,SUM(Q662,Y662,AG662)<=99.99),SUM(Q662,Y662,AG662),100)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

re: Your subject line: " Why is the answer an invisble 0"What does this have to do with the rest of your question?

re: "

This formula is in cell AV"AV is a

column, not acell.re: This portion of your formula: "

IF(N662=100,(Q663,Y663,AG663)"What is this supposed to mean? (Q663,Y663,AG663)

There's no function or operator associated with those cells. Excel will not know what to do with those cells.

It sounds like this is your criteria:

1 - If N662 in blank, then return a blank cell

2 - If N662 contains 100, then SUM(Q663+Y663+AG663)

2a - If SUM(Q663+Y663+AG663) <=99.99, then return that SUM

2b - If SUM(Q663+Y663+AG663) is not <=99.99, then return 100There is a one criteria missing:

What happens if N662 is NOT blank and does NOT contain 100? e.g. What do you want returned if N662 contains 50 or 87 or Bob?

Other than that final question, this formula appears to do what you want. However, if N662 contains e.g. 50 or 87 or Bob, the formula will return FALSE because we have not told it what to do if N662 is anything other than Blank or 100.

=IF(N662="","",IF(N662=100,IF(SUM(Q663,Y663,AG663<=99.99,

SUM(Q663,Y663,AG663),100)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

AV is a column, not a cell. - Thank you, that's what I meant.

re: This portion of your formula: "IF(N662=100,(Q663,Y663,AG663)"

What is this supposed to mean? (Q663,Y663,AG663)I am trying to get a SUM of the values in each

There's no function or operator associated with those cells. Excel will not know what to do with those cells.It sounds like this is your criteria:

1 - If N662 in blank, then return a blank cell

2 - If N662 contains 100, then SUM(Q663+Y663+AG663)

2a - If SUM(Q663+Y663+AG663) <=99.99, then return that SUM

2b - If SUM(Q663+Y663+AG663) is not <=99.99, then return 100There is a one criteria missing:

What happens if N662 is NOT blank and does NOT contain 100? e.g. What do you want returned if N662 contains 50 or 87 or Bob?

The formula in N662 will only allow for this cell to be 100 or blankOther than that final question, this formula appears to do what you want. However, if N662 contains e.g. 50 or 87 or Bob, the formula will return FALSE because we have not told it what to do if N662 is anything other than Blank or 100.

=IF(N662="","",IF(N662=100,IF(SUM(Q663,Y663,AG663<=99.99,

SUM(Q663,Y663,AG663),100)))

So, does the formula I suggested work for you or not?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi, no, unfortunately it did not. I noticed & corrected an error in my original formula (all referenced cells should be 662 opposed to 663)

I keep getting the "The formula you typed contains an error" with 3 bullets?

Also, to answer your original ?

1 - If N662 in blank, then return a blank cellCorrect

2 - If N662 contains 100, then SUM(Q663+Y663+AG663)If N662 contains 100, then I need to add a few conditions. (1) Add Q6621+Y662+AG662 IF the answer is 100 or more then value s/b 100. If the answer is 99.99 or less (but more than 0)then show sum of Q6621+Y662+AG662 (3) IF the sum is 0 then the answer is 100

2a - If SUM(Q663+Y663+AG663) <=99.99, then return that SUM

2b - If SUM(Q663+Y663+AG663) is not <=99.99, then return 100

message edited by HarrisLyfe2016

You've added one criteria, but still left out a possibility. You added the "if the SUM is 0, then return 100" but you haven't told us what to return if

the SUM is negative. If that is not a possibility, then this formula should work:=IF(N662="","",IF(N662=100,IF(AND(SUM(Q662,Y662,AG662)>0,SUM(Q662,Y662,AG662)<=99.99),SUM(Q662,Y662,AG662),100)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes, this formula worked because a negative number is not a possiblitiy! TY once again!

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History