Results 1 to 8 of 8

20031008, 02:32 #1
 Join Date
 Jun 2001
 Location
 Sacramento, California, USA
 Posts
 491
 Thanks
 0
 Thanked 0 Times in 0 Posts
If formula (WIN XP 5.1 SP1 Excel 2000)
I am still learning Excel, so please bear with me.
The attached xls shows in living color what I am trying to do.
I was able to figure out the first "IF" statement and get it to work.
I am missing something with the second one.
Could someone please look at the attached and point me in the right direction?
Thank you very much,
Michael Abrams

20031008, 04:37 #2
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
Michael
I looked at your worksheet and had difficulty following the logic of your 2nd question:
If the sum of b9:b11 is GREATER than b4, then b9 will equal the sum of b9:b11 minus b4
If you meant to say:
If the sum of b9:b11 is GREATER than b4, then b12 will equal the sum of b9:b11 minus b4
then I can help you.
There is a longer harder formula that I will give you that could go in b12 instead of yours. (Actually I am just adding another if clause to it.)
BUT there is a much shorter way to arrive at the same results. I will show you the long way just so you can see the use of nested if statements.
Either of these can be place in your cell b12 to give the same results:
Longer
=IF(SUM(B9:B11)<B4,(B4)SUM(B9:B11),IF(SUM(B9:B11)>B4,(SUM(B9:B11)B4),0))
shorter:
=ABS(B4SUM(B9:B11))
This uses the ABSOLUTE function to always give you the POSITIVE difference between the two amounts
Hope this helps
Paul

20031008, 05:08 #3
 Join Date
 Jun 2001
 Location
 Sacramento, California, USA
 Posts
 491
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
Whoa  thanks Paul!! I will have to study that at work tomorrow !
Here is a more detailed explanation of what I am looking for;
I receive a report that lists member ADDS, TERMS and CHANGES.
B2 is entered by me as 100 (Total reported adds)
B3 is entered by as 100 (Total reported terms)
B4 is entered by me as 700 (Total various types of changes)
So this could be considered the "Summary" of the report.
Then, lines 9,10 and 11 is the 'breakdown' of the 700 changes.
Now here is the fun...
Sometimes, 9 + 10 + 11 will equal less than the Total Changes. (long explanation why)
So, what I did, was create a 4th category "Misc".
The formula behind B12 basically says: If B9 + B10 + B11 is less than the Summary Total, then
place the difference in B12 so it reconciles. (Total Changes Summary 700 = Total Changes Detail 700)
That was pretty easy.
Now here is the catch.
Sometimes, after entering the count for B9, B10, and B11, the total equals GREATER than the Summary Total (again
long explanation how it occurs).
So, because the Detail Total has to equal the Summary Total, I want to take the "Difference" from DEMO (B9).
Using the attached as an example.
Let's say B4 was actually 550.
Once the entries for B9, B10 and B11 were made, B9 would become 90. (Thus B9:B11 would equal B4)
If this doesn't make sense, maybe I need to get some shut eye!!
I believe I did B12 correctly. If I am going about the solution totally wrong, feel free to
tell me so !!
Thanks again Paul. Sorry for the long winded post.
Good Evening from CA.
Home of Governor Terminator
Michael

20031008, 06:11 #4
 Join Date
 Feb 2003
 Posts
 363
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
It appears that what you are looking for is
1. First you enter cells B2B4
2. If the sum of B9B11 does NOT equal B4 (CHANGES), THEN
3. You want to adjust B9 (DEMO) to bring the sum of B9B11 back to equal b4
I have added some to your attached sheet that will get you started.
Hope this helps
BTW I see you have been having fun in tinsel town. Nothing like show biz to make a figure "known." Who knows? Maybe the TERMINATOR will follow Reagan to the White House !!

20031008, 10:51 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
If the user enters in B9 you could not put a formula to change it, you would have to have a button and macro. A "formula way" would be to LEAVE the data input in B9:b11 and add a "DATA Adj" column in Column C:
In C9:
<pre>=MIN(B4SUM(B10:B11),B9)</pre>
In C10:
<pre>=B10</pre>
In C11:
<pre>=B11</pre>
In c12:
<pre>=B4SUM(C9:C11)</pre>
The you just use the adjusted values in C9:C12
Steve

20031008, 15:43 #6
 Join Date
 Jun 2001
 Location
 Sacramento, California, USA
 Posts
 491
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
Paul:
Well, I gave the spreadsheet to my coworker, and he tweaked it just right based on your suggestions.
He got it working just as we needed.
I'm in Sacramento, so the circus is just beginning !!!
Steve,
I'm an Access man at heart, and prefer using buttons and code.
I will look at your suggestion and see what I can do.
I reallllly appreciate you guys helping out.
Michael

20031008, 16:15 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
This will adjust it, but must be run with a button or triggered by some other event.
<pre>Sub AdjustMe()
Dim af As WorksheetFunction
Set af = Application.WorksheetFunction
If Range("b4")  af.Sum(Range("B10:B11")) < Range("B9") Then _
Range("b9").Value = Range("b4")  af.Sum(Range("B10:B11"))
End Sub</pre>
The way I proposed earlier allowed you to keep what the user put into B9, the macro will overwrite it and not remember it. Also the formulas are live and will change as the input changes.
Steve

20031008, 17:52 #8
 Join Date
 Jun 2001
 Location
 Sacramento, California, USA
 Posts
 491
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (WIN XP 5.1 SP1 Excel 2000)
Steve  I like it !!!!!
Much appreciated.
Michael Abrams