Combining Formulas #2

I have two columns that get data from two different formulas.  The
reason I have two columns is becasue I couldn't figure out how to
combine the two formulas.  I worked on it for a while, but I had to
give up and divide it in half.

I'll post the two formulas.  Maybe someone can figure out an easy way
to combine them.  If you need more info, let me know.

Thanks.


=IF(C3="","",IF(B2="",IF(C3<=0,IF(D3<C3,B4,IF(B5="",IF(C3<=0,IF(D3<C3,B3,IF(B2="",IF(C3<=0,IF(D3>C3,B3,IF(B5="",IF(C3<=0,IF(D3>C3,B3)))))))))))))


=IF(C3="","",IF(C3>0,"",IF(B4="",IF(C3<=0,IF(D3<C3,B2,IF(B4="",IF(C3<=0,IF(D3<C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3))))))))))))))

0
jimx22 (226)
10/8/2008 5:25:14 PM
excel 39879 articles. 2 followers. Follow

3 Replies
364 Views

Similar Articles

[PageSpeed] 36

Can you explain in words what type of problem you're trying to solve?

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JimS" <jimx22@msn.com> wrote in message 
news:t2rpe4d6u3d4dmps5sflhuge99m648j292@4ax.com...
>I have two columns that get data from two different formulas.  The
> reason I have two columns is becasue I couldn't figure out how to
> combine the two formulas.  I worked on it for a while, but I had to
> give up and divide it in half.
>
> I'll post the two formulas.  Maybe someone can figure out an easy way
> to combine them.  If you need more info, let me know.
>
> Thanks.
>
>
> =IF(C3="","",IF(B2="",IF(C3<=0,IF(D3<C3,B4,IF(B5="",IF(C3<=0,IF(D3<C3,B3,IF(B2="",IF(C3<=0,IF(D3>C3,B3,IF(B5="",IF(C3<=0,IF(D3>C3,B3)))))))))))))
>
>
> =IF(C3="","",IF(C3>0,"",IF(B4="",IF(C3<=0,IF(D3<C3,B2,IF(B4="",IF(C3<=0,IF(D3<C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3))))))))))))))
> 

0
nicolaus (2022)
10/8/2008 7:09:01 PM
JimS <jim...@msn.com> wrote...
>I have two columns that get data from two different formulas. =A0The
>reason I have two columns is becasue I couldn't figure out how to
>combine the two formulas. =A0I worked on it for a while, but I had to
>give up and divide it in half.
....
[reformatted -- with comments]
>=3DIF(C3=3D"",
    "",
    IF(B2=3D"",              -- you've now checked whether B2 =3D "", you
don't need to do it again
      IF(C3<=3D0,            -- you've not checked whether C3 <=3D 0, you
don't need to do it again
        IF(D3<C3,          -- so D3 < C3 <=3D 0
          B4,

At this point you're returning B4 when C3 <> "", C3 <=3D 0 and D3 < C3.
But now you enter the ELSE portion of the *INNERMOST* IF so far, which
means C3 still <> "", C3 still <=3D 0, but now D3 >=3D C3.

          IF(B5=3D"",        -- you've now checked whether B5 =3D "", you
don't need to do it again
            IF(C3<=3D0,      -- unnecessary
              IF(D3<C3,    -- IMPOSSIBLE
....

At this point D3 must NECESSARILY be greater than or equal to C3, so
the rest of the formula will NEVER be evaluated. If you get to this
point, your formula returns FALSE. Similar things going on in your
second formula. The only thing clear is that you want to return "" if
C3 is "". You seem to want to return "" also when C3 > 0. If so, start
off with

=3DIF(OR(C3=3D"",C3>0),"",

Then it becomes murky. It looks like you want to return different
things when D3 < C3 and when D3 > C3. This omits what to do if D3 =3D
C3. You should either add equality to one or the other (so D3 <=3D C3 or
D3 >=3D C3) or add D3 =3D C3 as a separate condition to check.

You're also checking whether B2, B4 or B5 are "". It looks like you'd
usually be returning B3 except in particular circumstances in which
you'd return B2 or B4. Try explaining when IN PROSE when you'd want B2
and when you'd want B4. Then is should be possible to rewrite both
formulas into one similar to

=3DIF(OR(C3=3D"",C3>0), "",  IF(condition for B2, B2,  IF(condition for
B4, B4,  B3)))

This could actually be simplified to

=3DIF(C3<=3D0,  IF(condition for B2, B2,  IF(condition for B4, B4,  B3)),
"")
0
hrlngrv1 (375)
10/8/2008 9:45:22 PM
DETROIT LIONS	               47	45.5	52%	off	19%
MINNESOTA VIKINGS	-13	-13.5	48%		81%	-0.5
DETROIT LIONS	13.5
									
CHICAGO BEARS	-1	-3	95%	off	76%	-2   ATLANTA
3.0		
ATLANTA FALCONS	42.5	43.5	5%		24%	

MIAMI DOLPHINS	46	44.5	49%	off	33%	      FALSE
HOUSTON TEXANS	-3	-3.0	51%		67%	0



>Can you explain in words what type of problem you're trying to solve?

Probably not, but I'll try.  ;--)  I download this NFL data from a web
site, and put it into my spread sheet.  The two formulas I wrote work
fine, but I have to separate them into two columns.  I want to see if
I can combine the two formulas and put the answers into one column.

If the pointspread favorite is the bottom team, (Minnesota, -13,
-13.5) I have one formula and if the favorite (Chicago Bears, -1,-3)
is the top team I have another formula.  I couldn't figure out how to
combine the two.  The first number is the opening line, and the second
number is the current line.

So, in the first game (Detroit and Minnesota) the spreadsheet finds
the difference in the pointspread lines (-13 and -13.5) (-.5) and then
gives me the name of the team that is favored by the line move, in
this case Detroit, because since the line opened at -13 they are now
getting 13.5.

In the bottom game it spits out Atlanta because Atlanta's line went
from +1 to +3.

In my spreadsheet there is an empty row after and before each game.

If there is no line change (Miami, Houston) it comes back with a
false.

The other stuff in there is over/unders, money lines, etc.  They are
not necessary to my computations.

0
jimx22 (226)
10/9/2008 1:49:02 PM
Reply:

Similar Artilces:

Email forwarding #2
Is there anyway to get your outlook emails forwarded automatically???? Hi! Microsoft Outlook, not Express? 1). Tools\Rules and Alerts... 2). Our Redirect add-in (www.mapilab.com), it redirect email and new recipient will receive the full copy of message, including internet headers and the same sender as in original email. > -----Original Message----- > From: Jason [mailto:Jason@nts.uk.net] > Posted At: Wednesday, June 30, 2004 3:46 PM > Posted To: microsoft.public.outlook > Conversation: Email forwarding > Subject: Email forwarding > > Is there anyway to get your ...

Access Formula For Dates
I need to show on a weekly basis, the number of weeks left in the year. I don't want to have to enter it, I want Access to calculate it for me and show it on my report/database each time it is opened. For example , I want to say there are 34 weeks left in this year. Is there a formula I can usethat will determine the number of weeks remaining from today's date (whatever it is) to December 31? One way to look at this is that, if you can identify the "week" of the current date, you can subtract that from 52 to get an idea of how many weeks are left. .... o...

Upgrade Path sbs2000/sbs2003r2 and CRM1.2/3.0
We currently have CRM 1.2 installed on a sbs2000 server. We are looking to upgrade this server to sbs2003r2 and we also want to upgrade CRM from 1.2 to 3.0. Can someone recommend the best upgrade path? This is partial book smarts and partial experience... Book smarts: Upgrade to CRM 3.0 on the SBS2000 box (assuming SQL 2000) Experience: Redeploy to SBS2k3R2 box using CRM 3.0 redeployment tool Upgrade to Rollup 1 My experience was to take it from a 3.0 to 3.0 on R2, so I had a SQL 2000 -> SQL 2005 on a new domain. I recommend that you backup and follow the directions to a T. The up...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

Address Book Problem #2
Outlook version: 2003 When in a Distribution List, I click on [Select Members] and get this MS Office Outlook message: The list could not be displayed. The Contacts Folder associated with this address could not be opened; It may have been moved or delected, or you do not have permissions. For information on how to remove this folder from the Outlook Address Book, see Outlook Office Outlook Help. [OK] This is a recent problem. Advice? -- Larry Bohen www.audiobooksonline.com Sounds like you have an invalid reference to a Contacts Folder. reset those references here: Tools > E-mail a...

Setting up Outlook 2003 #2
I am in the process of setting up Outlook 2003 on my new computer. I believe I may have click the wrong answer and have put in incorrect information for outlook to start. I keep getting a box that ask for Username and passcode. When I put in the information I think it should be, I receive this message: Unable to open your default e-mail. The file C:\Documents and Setting\Owner\Local Setting\Application Data\Microsoft\Outlook\outlook.ost is not an offline folder file. Please tell me what I should do. When I click OK. Outlook closes. Thanks. -- Trying to keep up with technology G...

calendar #2
is there a formula for a continues monthly calendar? Hi tg not exactly sure what you're after here but if you enter 01/01/05 in a cell and drag down (autofill handle, little back cross bottom right of cell) it will increment by a day for each row. If you'ld like more assistance please feel free to post back with additional details. Cheers JulieD "tg" <anonymous@discussions.microsoft.com> wrote in message news:429d01c4afae$b110b910$a601280a@phx.gbl... > is there a formula for a continues monthly calendar? ...

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Sorting and coloring cells, formula or macro help needed
I have a problem that i don't know how to solve. I wrote it all in my excel file that needs to be done. File is here http://www.sendspace.com/file/q4jc71 Anything that isn't clear, pls ask me. I need this really badly, because my tables sometimes have thousands of those records. HTH: http://excel.tips.net/Pages/T002581_Sorting_Data_Containing_Merged_Cells.html Micky "domyrat" wrote: > I have a problem that i don't know how to solve. I wrote it all in my excel > file that needs to be done. > > File is here http://www.sendspace.com/fi...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

Automatic settling of EDC Batch #2
Is there a way to stelle an EDC batch automatically in RMS, or an add-on that accomplishes this? This would be very handy. Thank you in advance. ...

Notification if no external e-mail recieved for over 2 hours.
Recently the SMTP authentication settings were changed so that no external e-mails could be recieved. There was no way to see that this was the case until someone complained. Is there any way to monitor externally recieved e-mails so that if none are recieved after a certain amount of time for a notification to be generated? In this case a script using telnet could be used to check if e-mails could be recieved externally, but I was looking more for something like if no external e-mails recieved by any users for two hours then create notification. I can't seem to think of any way to che...

BillPay #2
When will Money start using the the free MyCheckFree.com? This a free service now. What gives. Boo Microsoft. I may need to switch. Yikes! So why am I socked with a $5.95 monthly fee? Just because I use it within the Money program? I don't use my bank so not sure why I get the fee. Thanks! "Bogee" <Bogee@discussions.microsoft.com> wrote in message news:C06B9868-442D-4841-98D3-0C85524F86ED@microsoft.com... > When will Money start using the the free MyCheckFree.com? This a free > service > now. What gives. > > Boo Microsoft. I may need to switch. ...

excel formula #4
I have in column A1:A365 all dates of the year 2005 and in colum B1:B365 the days of that date. In column D1:D365, E1:D365 and F1;F365 I fill in a X for every actio Succeeded. SO if it is 1 January 2005 and the action in D1 and E1 and F1 are al succeeded I fill in those fields an: X I have in a cell a formula what checks how many % (procent) succeede of the fields who are filled. But when it is December he also calculates the X that are filled in A etc. Now I would like to make a formula which calculates how many action went good in the last 30 days is this possible? So that he only chec...

Dynamic formulae
Hello Excel disciples, =A0 =A0 I wonder if I can run this past you? =A0 =A0 Lotus 123 had a facility that you were able to build "Dynamic" formulae ie; =A0to construct using text and ranges to create valid formulae. =A0 I have several worksheets name Team 1, Team 2, Team 3, each identical etc I have a consolidation worksheet that takes numeric values from a cell from= each and sums the total. =A0 In the Totals sheet columns above the values column is the team name in= text. =A0 =A0 =A0Team 13 =09=09Team 14=09=09Team 15 etc =A0Grd1 =A0Grd2 =A0100 =A0=09375 =A0100=09=09375 43.5=09=09...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

Time Formula #2
Hi I run Excel 2K I download data from a mainframe. This data has a date & time format in it (dd/mm/yyyy hh:mm:ss) I work in a place that has a 3 shift cycle - day shift, afternoon shift, night shift. Day shift starts at 7:20am and ends at 15:29pm Afternoon shift starts at 15:30pm and ends at 23:19pm Night shift starts at 23:20pm amd ends at 7:19am I need a formula that looks at the cell with the date/time in it and displays the word DAY (for the time frame of day shift), AFTERNOON (for the timeframe of afternoon shift) and NIGHT (for the timeframe of night shift) In my previous ...

Evaluating text as formulae
Is it possible to refer to text in a cell as though it were a formula, ie execute it instead of displaying it? Eg if the result of your formula was "A1 + B2", to actually add A1 and B2 and display the result? Thanks Nigel Like concatenating text? =concatenate(a1," + ",b2) or =concatenate(a1,b2) =concatenate(a1," ",b2) "Nigel Ramsden" wrote: > Is it possible to refer to text in a cell as though it were a formula, ie > execute it instead of displaying it? Eg if the result of your formula was > "A1 + B2", to actually add A1 and...

Formulae when some cells contain #N/A
How do I sum cells that include #N/A in the range. The #N/A is the result of a lookup formulae. I want such cells to register as 0. Currently the sum of the cells gives #N/A as the answer. Thanks Heather Use the ISNA function, thus =IF(ISNA(ExistingVlookup),0,ExistingVlookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "HeatherC" <HeatherC@discussions.microsoft.com> wrote in message news:0B8888EE-F35C-4F5A-B1DE-BCF24DF870E6@microsoft.com... > How do I sum cells that include #N/A in the range. The #N/A is the r...

convert #2
I have my checks on version 6 of quicken. It's backup file is qdb. It wouldn't run in this new vista machine so I bought the 2009 version. It says in black and white in a hard to find instruction that it has changed format and will not import my old files, it uses qif. I returned it and got my refund check today. How about that? My question. If ms money will import my old files, I want to buy it. If it will not, I don't want it. I have been looking and can not find anywhere it says one or the other. Glenn In microsoft.public.money, Glenn wrote: >I have my checks on ...

forecast function help #2
Just to clarify that formula, am i right to say this? =ROUND(FORECAST($E$1,B2:D2,$B$1:$D$1),0) $E$1 - is selecting the forcast for year 2004 B2:D2 - is selecting all the data bar the years in the top row $B$1:$D$1 - is being used to predict the outcome for 2004 Is that right? Thanks again -- metho ----------------------------------------------------------------------- method's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1503 View this thread: http://www.excelforum.com/showthread.php?threadid=26644 Hi B1:D1 are the x values there B2:D2 the associated y ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Fill values for formula
I am trying to write a formula that will do the following: if a cell i fill in with color, do not sum values, otherwise sum values. However I do not know what the fill variable is to put in the formula. Hop this make sense. Please help. Thank You -- Message posted from http://www.ExcelForum.com Hi gardener! See: Chip Pearson: http://www.cpearson.com/excel/colors.htm Watch out for colors that result from conditional formatting as they are treated differently from "manually" imposed colors. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Exc...

Q) Last row/column ------ excel formula query
Kindly suggest what is the best way to reference the following in excel formulas: +) last row of a worksheet that has some data +) last column of a worksheet that has some data Regards, Sandeep Hi Sandeep In VBA the best thing is to use this functions You can use this in your code then for example Lr = LastRow(Sheets("Sheet2")) Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:...