Excel - IF function not quite working... 3

[sohunter]

I have four cells that get user input, and they ultimately need to add up to 100% (1). The cells are formatted as percentages, with zero decimal places. In a cell off to the side, I want to alert user if their sum doesn't add up to 100%. I'm currently using:

=IF(SUM(C43:C46) = VALUE(100%),"Adds up","Please note - your proportions don't add up to 100%&quot

I've also tried:

=IF(SUM(C43:C46) = 1, "Adds up", "Please note - your proportions don't add up to 100%&quot

Either way, it's showing the second result ("please note...&quot even when the percentages do add up to 100%. Any advice on why? Thanks much.

SH

 

[LangAdel]

Hi sohunter

Tried your second formula, and I don't have any problems in making it work. (asuming that you seperate the argumentsin the formulan with ; and not ,)
Could be your cellreferences which are not correct?
regards
Brian

 

[DaleWatson123321]

Hi SH,

Your formmulas should work. I've tried your second one and it DOES work.

=IF(SUM(C43:C46)=1,"Adds up","Please note - your proportions don't add up to 100%&quot

I therefore suspect your problem might be with there being a "trailing" decimal that is not visible because of the formatting.

Please appreciate that with whatever formatting you specify, Excel will "show" the formatting specified. HOWEVER, the "actual" number(s) entered in the formatted cells can have decimals that will not show - simply because of the formatting - that instructs Excel to "only display" the number of decimals instructed by the formatting specified.

But the "actual" number entered into a cell is "still" the number used by Excel in any calculation - regardless of the type of formatting.

For example, if the 4 numbers in your range all show 25%, but one of the numbers entered is actually 25.1, then it will still show as 25%. But the total will be 1.1 (not 1), and thus your condition will be "false".

To check this, you can place your cursor on each cell, and then view the actual number on the "Edit" line.

Or, you could enter =SUM(C43:C46) in a separate cell and format that cell to show more than 0 decimal places. It's likely that you'll see a trailing decimal.

Here's another "tip" regarding the use of "Conditional Formatting to display your error message.

In your cell with the formula
=IF(SUM(C43:C46)=1,"Adds up","Please note - your proportions don't add up to 100%&quot
...you could use Conditinal Formatting to "enhance" your message - this way...

1) From the menu, use: Format - Conditional Formatting.
2) Under Condition 1, change "Cell Value is" to "Formula Is", and then in the space opposite, enter this formula...
=SUM($C$43:$C$46)<>1
3) Now click on &quot;Format&quot;, then the &quot;Pattern&quot; tab, and choose (for example) RED as the color, and choose YELLOW as the Font color.
4) Click &quot;Add&quot; to add a second condition.
5) Under Condition 2, change to &quot;Formula Is&quot;.
6) Opposite Formula Is, enter this formula...
=SUM($C$43:$C$46)=1
7) Click &quot;Format&quot;, then &quot;Pattern&quot;, and choose GREEN as the color, and leave BLACK as the Font color.

Now, with this Conditional Formatting, the user will be presented with a RED &quot;Stop-like color&quot; when the total doesn't add to 100%, and a GREEN &quot;Go-like color&quot; when it does add to 100%.

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

If they MUST add up to 100% then I would not allow the user to enter the last cell, only the first three. Use a formula for the fourth cell:
[blue]

C46: =1-SUM(C43:C45)

[/color]
Unlock cells C43:C45 (and any other that require user input) and protect the worksheet.

Then use Dale's tip to use conditional formatting for cell C46 when it is negative, and/or use this modified form of the formula to display an error if the first three percentages are too high:
[blue]

C46: =IF(SUM(C43:C45)>1,"Over 100%",1-SUM(C43:C45))

[/color]

 

[sohunter]

Thank you everybody.

Well...the problem was indeed my references. (Thanks Brian. ) Man, that's embarrassing. Was referring to the wrong column.

However, I'm glad I asked because everybody's replies/further advice is helpful.

SH