Solved Why is the answer an invisble 0?

June 21, 2016 at 16:32:26
Specs: Windows 64
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 IF N662 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 100

If 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!


See More: Why is the answer an invisble 0?

Report •

✔ Best Answer
June 21, 2016 at 19:29:18
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.



#1
June 21, 2016 at 17:37:13
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 a cell.

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 100

There 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.


Report •

#2
June 21, 2016 at 18:08:59
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 100

There 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 blank

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)))



Report •

#3
June 21, 2016 at 18:18:39
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.


Report •

Related Solutions

#4
June 21, 2016 at 19:02:41
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 cell Correct
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


Report •

#5
June 21, 2016 at 19:29:18
✔ 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.


Report •

#6
June 28, 2016 at 10:50:35
Yes, this formula worked because a negative number is not a possiblitiy! TY once again!

Report •

Ask Question