Comparing two numbers

I am trying to figure out how to compare two numbers (that are
constantly changing in value) to see if they meet a particular ratio
and then pick the smallest of that original number to be carried
forward.

I have tried nesting If statements but end up with "False" answers
instead of 0 sometimes.

This is the type of data I am tring to compare along with the
conditions that must be met.

Example 1.

a.   166.00
b.    97.00

First condition:  If B>=1/3A
Second condition:  If B meets first condition then B must be larger
than C.
Third condition If B meets second condition then C is carried forward
to the cell.
If B< 1/3A then 0 should be placed in the cell.

The problem occurs when the numbers get switched around.  If the number
order stayed the same, I could use an If statement.  But the numbers
will come in any combination for example:

Example 2.
d.  500          I need this one to end up with 500 being carried
forward
e.  550

Example 3
f.   100          This one will have 0 carried forward
                      g.   600

Example 4
h.   250         This one will have 250 carried forward.
i.    750

I am stumped.  I have looked through every help option and blog I could
find.  I would appreciate help.  Thanks in advance.

Carrie

0
chughes (1)
3/30/2006 3:38:52 PM
excel 39879 articles. 2 followers. Follow

1 Replies
611 Views

Similar Articles

[PageSpeed] 38

Carrie,
You might simplify the problem by making two additional columns, where
one column has the greater of the two and the other column has the
lesser.

If your numbers are in A1 and B1, then C1 could be =max(a1,b1) and D1
could be min(a1,b1).

That ought to simplify the if statement you're looking for.

0
3/30/2006 4:18:34 PM
Reply:

Similar Artilces:

blue row numbers in excel #2
I have blue row numbers and hidden rows. I have read that it is due to grouping but I don't see any signs of grouping. There are no numbers or anything at the top. I can manually resize the hidden rows but not by the format menu. I can't unhide the rows by right click or the menu. Can anyone help me? Also, when I manually resize the rows and save it then close it and go back to it the rows are hid again. ...

Adding numbers from sheet 2 to sheet 1
Basically I have a list of names down the sheet and across the sheet by months I have a number. Each column (month) is added and put in bottom cell for that column, ie., =sum(e3:e50) The second sheet is the same but a different list of names. I also sum these by month at the bottom. How can I change the formula for each column on sheet 1total to include the corresponding column total from sheet 2? Many thanks. -- David Hi David Either =SUM(E3:E50)+SUM(Sheet2!E3:E50) 0r =SUM(E3:E50)+Sheet2!E51 Assuming E51 holds the sum of the values in sheet2 -- Regards Roger Govier "caseysmyd...

charting two sets of data at the same time
I have a set of data which is comprised of time vs height (x,y) 1000 pts I have a subset of that data 20 pts where I would like to put in markers. I can't seem to get the scale of one with respect to the other correct. What I'm looking for in the end is a graph that looks like a bell curve with 20 or so places on it marked in some way. thanks robert ...

question about removing text from cells, leaving numbers
This forum has already proved helpful, I've already gotten help for a problem. I've got another problem, however. I've got a large number of cells with both numbers and text, I need to extract the text, leaving the numbers so I can add them. The problem is, the number of characters varies. Here are some examples of cells: "3 I am so sad or unhappy that I can't stand it., 2 I am sad all the time and I can't snap out of it., 1 I feel sad." I need to remove the text, leaving the 3 and the two, then I could do text to columns to get the 3 and 2 into their own c...

linking pages in two different work books
trying to link two different pages in two seperate workbooks. filling in statistical data in first workbook in specific fields. then having to open the next work book and insertin the same in formation again. don't wnat to be redundent and would lik to populate worksheet in workbook 2 with the same input from workshee in work book -- Message posted from http://www.ExcelForum.com I am also in need of this ability. I have a list on one sheet and a chart with the names from the list o another, same workbook. I have been linking each individual name fro the list to their location on the ...

comparing excel files #2
We have two versions of the same Excel workbook and need to merge the two. These files are huge and comparing them manually would take days. How can I compare the two files to see what is different? Windows 2000 5.00.2195 Service Pack 3 Excel 2002 (10.3506.3501) SP-1 Thanks! Hi, On July 6, Dave Peterson wrote this: jeff ------------------------------------------------ It depends. Myrna Larson and and Bill Manville wrote an addin that compares two worksheets on a cell-by-cell basis. A1 is compared with A1. B99 to B99. If one of the users inserted (or deleted) rows (or columns)...

Formatting cells to show two dates in one cell
I have a column in my spreadsheet that is for the date that I recieve info from clients. When I recieve this info on more than one date, I've been putting a comma between the two dates. I have a word document that I use merge to pull this information (along with other info), but when I pull the data from this particular column, it changes the dates to something like 12:00AM. I've tried changing the formatting of this column to text and also to general, but it still will not give me just the two dates. I don't want to create another column for the second dat...

Page Number Problem
Hi there, hope you are fine... I'm new in this forum, and I have a doubt (a big doubt) about page number... Here is the problem: I have a long spreadsheet (about 320 pages), and I need to show the the current page number, in a sigle cell (A1, for example)... So, if a cell in page 1 is selected, in A1 we'll have "Page 1 of 320", otherwise if the selected cell is in page 20, now A1 will be showing "Page 20 of 320", and so on... I've tried use the PageBreaks.Count, but I get always the same result: "*Page 1 of 320*", it doesn't change to "15 o...

comparing two fields
Hello: In table A and B, I have two very similar varchar fields with subtle differences. In table A, I have field A that begins with the phrase "CTR". In table B, I have field B that also begins with the phrase "CTR". Both fields have 0's after "CTR", but there is no set number of 0's. After the 0's, there are anywhere from 3 to 4 numeric characters. Table B's field B sometimes ends with a hyphen followed by a few numbers, and sometimes field B ends with an alpha character. Let me explain. For example, Table A field A could be CTR0000...

week numbers
Hi I am trying to insert the week numbers i would like the week to start on a Saturday and end on the Friday i have tried this formula =TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+ MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7) where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday). my formula :- =TRUNC(((B21-DATE(YEAR(B21),1,1)+MOD(7-WEEKDAY(DATE(YEAR(B21),1,1)),7))+6)/7) on chip pearsons web site what i am finding when i get to Friday of the first week and the week number changes to 2, later ...

How Can You Put Two Lines of Text in Same Cell?
I want to put two (or more) lines of text in the same cell. Like Network Command Is there any way to do that? Dick If you're typing the value, use alt-enter to force a new line within the cell. If you're using a formula, you can use: =a1&char(10)&b1 (format the cells to have wrap text, too.) Dick wrote: > > I want to put two (or more) lines of text in the same cell. Like > > Network > Command > > Is there any way to do that? > > Dick -- Dave Peterson Easily! Enter your first line of text, press <ALT>-<En...

Store sizes and number of mailboxes.
with Exch2003 SP2 what is the fastest way to find the information in the subject? Does MOM tellyou the store size and number of mailboxes? On Feb 9, 1:16 pm, Tyson <T...@discussions.microsoft.com> wrote: > with Exch2003 SP2 what is the fastest way to find the information in the > subject? Does MOM tellyou the store size and number of mailboxes? My firm uses a Quest Product called MessageStats. There are tons of reports you can run such as mailbox sizes, number of mailboxes, activity information per mailbox, server activity, etc... Its a nice tool, I use it every day. On ...

Page numbering with Master Pages
I have two Master Pages. One is to have no numbers on TOC and other pages. I noticed in creating a new book that the page numbering is starting on page 8 as page 8, instead of it being page 1. It's the 8th page, but the first 7 pages have another Master Page applied. How do I get page 8 to start the book's numbering off as page one? -- -- Robert Pearson ParaMind Brainstorming Software http://www.paramind.net Creative Virtue Press/Telical Books/Regenerative Music http://www.rspearson.com paramindsoftware@gmail.com wrote: > I have two Master Pages. One is to have no numbers on ...

Change default number format
Anyone have any idea how to do this? You could create a template workbook (*.xlt) and format all the cells with the numberformat you want. Then use that whenever you want to use that special format. If you want to use that number format for all new workbooks, name the template workbook book.xlt and store it in your XLStart folder. If you save another copy as sheet.xlt, then any new sheet added to an existing workbook will inherit that number format. mbrucker wrote: > > Anyone have any idea how to do this? -- Dave Peterson ec35720@msn.com Right click the cell. > format cells ...

How can I change a serial number?
Once I've entered and commited a serial number, is there are any way I can change it later? El, No, you can only delete it and re-add it with the new serial number with the standard functions of RMS. There is an add-on from Digital Retail Solutions called the DRS Serial Editor you can get; http://www.digitalretailer.com/rmsaddins.htm -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on ...

Exporting OL2k contacts so street number and name go into different fields?
Our local residents association has each subscriber listed as a separate contact in Outlook 2k. We want to print labels for a mailing by volunteers and it would be a big help to pre-sort the envelopes by street name first. Problem is I can't see how to do it. I tried exporting the contacts as a 'Windows csv' file and opened it in Excel 2k hoping to sort on street name. But, although town and postcode appear correctly in the fields 'Business City' and 'Business Postcode', all of the rest, say '1 Acacia Gardens, Suburbia', goes into 'Business Street'...

Entering a number series into a column
Hello, I am using Excel 2002 and I am having an issue with what I think shoul be a simple procedure. What I need to do is insert a series of number into a column to associate an alarm message with a number, Example: These is the contents of column C. C Row1 Comp A: Flowmeter HI Temp Row2 Comp A: Flowmeter HI HI Temp Row3 Comp A: Flowmeter LO Temp Row4 Comp A: Flowmeter LO LO Temp Row5 Comp A: Flowmeter LO LO LO Temp I need to add a series of numbers to the beginning of the text, en re...

Two Versions
Hello, Is it possible and easy to have two versions of Excel on the same PC? If so, how? Thanks. Bill Hi Bill This is no problem. See this KB http://support.microsoft.com/default.aspx?scid=kb;en-us;214388 -- Regards Ron de Bruin http://www.rondebruin.nl "Bill" <whmc2@ix.netcom.com> wrote in message news:eGSkrYy1EHA.1264@TK2MSFTNGP12.phx.gbl... > Hello, > Is it possible and easy to have two versions of Excel on the same PC? If so, how? > > Thanks. > > Bill > Hi Bill Sure. This one has six Excel versions running, sometimes simoultanously. ...

Auto Numbering in Excel
Hi Can anyone please tell me if it is possible to automatically number rows in an excel spreadsheet. Eg: 1 1.1 1.2 2 2.1....etc However there will be blank rows in between the numbered list. At the moment I am manually entering the numbers but then if I need to insert an additional section, I have to manually change all the following numbers. Is there any easy way to auto number in excel. Any help/advice most welcome. Thanking you in anticipation. Hi no this is AFAIK not supported in Excel -- Regards Frank Kabel Frankfurt, Germany Sue wrote: > Hi > ...

how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...

Sorting by number of characters
I am using Excel 2007 (thought I posted this earlier, but can't find it - even with google group search). I need to sort an inventory list according to the number of characters in the item number (not by numerals). Is this possible? Thanks! Nicole May be this... =LEN(A1) Change the A1 to your Item Number Cell. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "NicoleS" wrote: > I am using Excel 2007 > (thought I posted this earlier, but can't find it - even with google group ...

ID number showing
I created a report that collects its data from a query. When I run the query the correct information displays. When I run the report one of the fields displays the ID number of the data table rather than the text entered into the table. -- bmorganh Is there a chance that the field in question is defined as a "lookup" data type in the table? If so, Access is displaying "the text [...in...] the table", but is actually storing the ...!ID number! More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have r...

Connecting two activities
Hi, Is it possible to connect an activity (for example an email) to other activities (as tasks)? I've created an activity, and now want to be able to show that as a result few activities have been created. How can I link between activities? Thanks, Naama Axonom's Core Module allows you to create m:n relationships between Activities; Contacts; Accounts; etc. Thus you can follow a string of events, or create a Organization Chart in MSCRM. Its an easy to install module . Their product suite is certified by Microsoft, and contains a number of powerful horizontal applications as well ...

Comparing Data 05-27-07
Hello all, I need to write a database for work that can compare and report differences of settings in a PLC. The PLC saves the data in a paradox file. I can pull information into access but I am unable to change the way the data is stored. PLC table is stored; Machine Number, Setting No., Setting Name, Setting, Setting Page, Setting No. On Page 12345, 1.1, Max Speed, 3500, 1, 1 12345, 1.2, Min Speed, 3000, 1, 2 12345, 1.3, Selected Components, 10010000111, 1, 3 The Table I need to set up looks like this; Machine Number, Setting 1.1 value, Setting 1.2 value Setting 1.3 value 12345, 3500,...

Numbered paragraphs with multiple 're-start at 1'.
I have a 70-page test specification with heading numbering to level 3 (using built-in styles). Each step of the test procedure is numbered using a separate list with a style called 'Step'. My reasoning for doing this is that sometimes the test procedure paragraphs are at level 3 and sometimes at level 4. I can't have some level 3 paragraphs as headings and others as test steps. The test steps re-start at 1 after every new heading. Occasionally, it is not possible to re-start at 1 - the numbering just refuses to be changed, and I can't work out why. Twice during th...