Adjusting one data set based another

I'd like to compare two sets and report a result that shows how one data
set would plot if related to the other set (sorry, I know that's poorly
worded). Here's an example:

Sample #1               Sample #2
25%	0.26	25%	0.264
30%	0.31	30%	0.317
35%	0.36	35%	0.365
40%	0.41	40%	0.426
45%	0.45	45%	0.5
50%	0.51	50%	0.569
55%	0.56	55%	0.644
60%	0.62	60%	0.72
65%	0.69	65%	0.779
70%	0.75	70%	0.835
75%	0.84	75%	0.901
80%	0.93	80%	0.968
85%	1.03	85%	1.049
90%	1.12	90%	1.108
95%	1.24	95%	1.194
100%	1.4	100%	1.242
			


So, in the above data for example, at 50%, sample #1 reports 0.51 in
sample #1 and 0.56 in sample #2. 

What I want to do is create a formula that compares sample 1 to sample
two and gives me the dot percent value where sample one needs to be to
equal sample two. For example for 50%, sample #1's value of 0.51 would
equal a percent value around 46%, as this is the area that a value of
0.51 would occur. Any suggestions on how to achieve this?

m


-- 
meddington
------------------------------------------------------------------------
meddington's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27012
View this thread: http://www.excelforum.com/showthread.php?threadid=429071

0
9/6/2005 12:16:55 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
606 Views

Similar Articles

[PageSpeed] 52

I'd develop equations that predict each Sample and then go from there. 
I've plotted the data and one appears to be more of an exponential and the 
other linear.

"meddington" <meddington.1ux5qe_1126009360.8292@excelforum-nospam.com> wrote 
in message news:meddington.1ux5qe_1126009360.8292@excelforum-nospam.com...
>
> I'd like to compare two sets and report a result that shows how one data
> set would plot if related to the other set (sorry, I know that's poorly
> worded). Here's an example:
>
> Sample #1               Sample #2
> 25% 0.26 25% 0.264
> 30% 0.31 30% 0.317
> 35% 0.36 35% 0.365
> 40% 0.41 40% 0.426
> 45% 0.45 45% 0.5
> 50% 0.51 50% 0.569
> 55% 0.56 55% 0.644
> 60% 0.62 60% 0.72
> 65% 0.69 65% 0.779
> 70% 0.75 70% 0.835
> 75% 0.84 75% 0.901
> 80% 0.93 80% 0.968
> 85% 1.03 85% 1.049
> 90% 1.12 90% 1.108
> 95% 1.24 95% 1.194
> 100% 1.4 100% 1.242
>
>
>
> So, in the above data for example, at 50%, sample #1 reports 0.51 in
> sample #1 and 0.56 in sample #2.
>
> What I want to do is create a formula that compares sample 1 to sample
> two and gives me the dot percent value where sample one needs to be to
> equal sample two. For example for 50%, sample #1's value of 0.51 would
> equal a percent value around 46%, as this is the area that a value of
> 0.51 would occur. Any suggestions on how to achieve this?
>
> m
>
>
> -- 
> meddington
> ------------------------------------------------------------------------
> meddington's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=27012
> View this thread: http://www.excelforum.com/showthread.php?threadid=429071
> 


0
reply6075 (299)
9/7/2005 12:24:54 AM
Graph each series and add a trendline to each.   For Sample 1, make it a 
linear series and for sample 2, make it an exponential series.   You can 
format each trendline to display the equation on the chart.
"meddington" <meddington.1ux5qe_1126009360.8292@excelforum-nospam.com> wrote 
in message news:meddington.1ux5qe_1126009360.8292@excelforum-nospam.com...
>
> I'd like to compare two sets and report a result that shows how one data
> set would plot if related to the other set (sorry, I know that's poorly
> worded). Here's an example:
>
> Sample #1               Sample #2
> 25% 0.26 25% 0.264
> 30% 0.31 30% 0.317
> 35% 0.36 35% 0.365
> 40% 0.41 40% 0.426
> 45% 0.45 45% 0.5
> 50% 0.51 50% 0.569
> 55% 0.56 55% 0.644
> 60% 0.62 60% 0.72
> 65% 0.69 65% 0.779
> 70% 0.75 70% 0.835
> 75% 0.84 75% 0.901
> 80% 0.93 80% 0.968
> 85% 1.03 85% 1.049
> 90% 1.12 90% 1.108
> 95% 1.24 95% 1.194
> 100% 1.4 100% 1.242
>
>
>
> So, in the above data for example, at 50%, sample #1 reports 0.51 in
> sample #1 and 0.56 in sample #2.
>
> What I want to do is create a formula that compares sample 1 to sample
> two and gives me the dot percent value where sample one needs to be to
> equal sample two. For example for 50%, sample #1's value of 0.51 would
> equal a percent value around 46%, as this is the area that a value of
> 0.51 would occur. Any suggestions on how to achieve this?
>
> m
>
>
> -- 
> meddington
> ------------------------------------------------------------------------
> meddington's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=27012
> View this thread: http://www.excelforum.com/showthread.php?threadid=429071
> 


0
reply6075 (299)
9/7/2005 12:27:24 AM
> I'd develop equations that predict each Sample and then go from there.
> I've plotted the data and one appears to be more of an exponential and
> the
> other linear.>[/ 

I came up with an equation that will give me the results I need, but
now I'm having trouble implmenting it into a form that excel likes. 

Its basically two formulae, one for the situation where the value of
sample 2 is less than the value of sample 1, and one for when the value
of sample 2 is greater than the value of sample 1.

=IF(B15"<"D15,(((D15-B15)/((B16-B15)/(A16-A15)))+A15)),IF(B15>D15,(((D15-B14)/((B15-B14)/(A15-A14)))+A14))


But apparently I don't have it formatted correctly as I'm getting an
error. No if I use the formulas individually, they work as expected. 
=IF(B15"<"D15,(((D15-B15)/((B16-B15)/(A16-A15)))+A15))
or
=IF(B15>D15,(((D15-B14)/((B15-B14)/(A15-A14)))+A14)) 
But when I put them together, error. So, is there a problem with the
way I established the second IF statement? Any thoughts?

> Graph each series and add a trendline to each. For Sample 1, make it a
> linear series and for sample 2, make it an exponential series. You can
> format each trendline to display the equation on the chart. 

I did this, but I'm not sure how to interpret the results.  :confused:


-- 
meddington
------------------------------------------------------------------------
meddington's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27012
View this thread: http://www.excelforum.com/showthread.php?threadid=429071

0
9/7/2005 6:48:36 PM
Reply:

Similar Artilces:

Printing just one page?
I would like to print just one page in my publication, at times. When I hit file, print, I do not have a print option. Print merge, print preview are all that is available. Can anyone help? Please?? What version Publisher? When you are ready to print one page, there should be check off for "current page". Are you expanding the File menu? Right-click any toolbar (Publisher 2002), customize, toolbars tab, check *Always show full menus." What happens if you ctrl+p or click the print icon on the toolbar? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msau...

Matching two sets of data
Hi all, I have two spreadsheets and both spreadsheets contain a number o projects. One contains the name and info about them and the othe contains the costs for them. Each project is identified with a code But in one spreadsheet the code is, for example, 000012.R. And i another is 000012. I need to match up those codes within eac spreadsheet and insert the correct cost against the respective projec in the project info spreadsheet. Can anybody tell me how to go about this or how to get started on it. If you need more info just say! Any help will greatly be appreciated! Thank Yo -- Message...

Price adjustments
I have a client that would like to adjust prices based on a markup scheme from cost. The scheme is as follows: If cost = $0.01 - $5.00, the markup is X%, if $5.01 - 25.00 then markup is y, ect ,ect. I figured I would use a SQL statament with the UPDATE command, but Im not exceptionally proficient in SQL, so could someone provide me with a SQL statement please? Thank you. Phil - before you decide to embark upon and join the dark side..... Have you tried using the inventory wizard? Run task 110 change item prices, select "filter" as your method, in the filter use "pric...

How can I import "Packed" mainframe data into Excel?
I've copied a file from an IBM Mainframe computer to my PC and need to do some analysis in Excel. Some of the fields in the file are "packed" - for example, the format of one field is " P(9.2). On the mainframe, that takes up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC 8-bit) into two, 4-bit parts and stores one decimal value in each part. Suggestions about how I can get this format into Excel without having to re-format the data in a mainframe ...

Contents automatically entered into another cell
working on an estimate and trying to figure out a way to take the results of one cell and have its contents be automatically entered into another cell. I have a subtotal line item for a particular category, I am wanting to create a cover page showing the various catergories but not all of the breakdown. (It is simple to keep track of right now, but as the estimate gets more involved, changes become harder to remember). I'm trying to create a reference to the cover page that will reflect the sub-total line item from the detailed breakdown. So, if $3.23 is shown in the deatailed br...

Moving material from one page to another
I'm using Office 2007/Publisher. How do I move material from one page to another? Cut and paste doesn't seem to work very well in this version of Publisher. Example: I have revised the document and now have a page I don't need. Some of the material on the page is in a Master but not necessary to move. Other stuff consists of text and text with pictures that I want to reposition on an earlier page. Have you tried dragging the objects to the scratch area? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Saguaro...

import data to specific columns
Hi- I'd like to import the data in my tab-separated text file into *certain* columns of my spreadsheet. Excel imports the data into adjacent columns, but is there a way to import into non-adjacent columns; e.g., import data into columns A, D, and G? Thanks. -- marlea ------------------------------------------------------------------------ marlea's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26209 View this thread: http://www.excelforum.com/showthread.php?threadid=395159 How about importing them to a different worksheet, then copying the values where y...

sql server data to ms word using xml
hi i was given a task to display sql server data in ms word using xml. Im currently doing research to accomplish the task. What i have in mind is to create an xml template (or schema?) to load the sql server data first.Then add the xml template in the ms word add-in option. Is it possible? Can anyone point me to the right direction? Thanks My approach to this for the last few years (since Office XP) has been to save a Word document formatted they way it should end up - as XML. And then rename it to a XSL and put in iterators to populate the data from the XML queried out of SQL Serve...

Comparing two sets of data
I need to compare sets of data in two different spreadsheets in two different columns labeled the same, Grant Number. We are trying to track if both sets have the same grant numbers or if there are duplicates. Did you adjust the ranges from column Z to whatever column held your data? (and did you remove the extra trailing close parenthesis?) Another formula that I use is this: I insert a helper column to the right of the Key data. Then I use a formula like this in Grant1 (assumes data is in column B of both sheets). =IF(ISNUMBER(MATCH(B2,Grant2!$B$2:$B$9999,0)),"Match",&quo...

unwanted: Zoom adjust when adjusting Print Scale
Would someone be willing to review the file and advise. when in Print Preview - if I adjust the print scale - then the zoom ( screen view ) adjust and not the printed view. I have several co-workers which have had the same issue happen to them. It has to be a file setting - as other excel files do not do this. When I go to Page Break Layout and try to drag the blue dotted line - nothing happens, it will not move - or - it moves and gives me an error "Margins do not fit page size" and I will get blue dotted lines around all cells and 100's of pages. Have you change the page ...

making adjustment for the start bar on the side of the screen
I want my 2 windows to be tiled vertically. Window1 is on the left and needs 25% of the screen Window2 is on the right and needs 75% of the screen I've done this with something like the code below, but this doesn't take into account if the user has the start bar on the side. Is there a way to tell if it is, and how many pixels it's taking up? thanks, southernatheart lngHorizontal = System.HorizontalResolution lngVertical = System.VerticalResolution 'RESIZE THE EXISTING DOCUMENT Application.WindowState = wdWindowStateNormal Application.Move Left:=0.2 * lngHorizon...

Move one shape independent of others
How can I move one shape without having all the shapes connected to it to also move? For example, in an org chart I may want to move a supervisor shape up so I can put a note under the box. Every subordinate also moves up so I have to seletc them all and move them down. Hi Karl, I've looked for options and tried a few ideas, but it look like you're stuck -- you've got to select all the subordinates and move them : ( -- Hope this helps, Chris Roth Visio MVP Free Visio shapes: http://www.visguy.com/category/shapes Visio programming info: http://www.visguy.com/category/pro...

Appointments in OWA off by one hour
Hi, We are running Exchange 5.5 as our corporate mailserver with OWA for some of the sales persons. I recently installed Outlook 2003 on two new computers. After a few weeks one of the persons using Outlook 2003 conplained that the appointments he booked through OWA showed up an hour later when he came in to the office and opened Outlook. I checked, and he was right. If I create an appointment starting at 13.00 and ending at 14.00 through OWA, it shows up as starting at 14.00 and ending at 15.00 in Outlook. If I log back into OWA, the appointment is still showing 13.00-14.00. This only seem...

Pending Account Adjustments
I made a few account adjustments (credits) at HQ for a customer a couple of weeks ago. I just checked at the store level and the customer balance is not updated and the customer can't put items on his account because the credit limit is reached and the credits are not reflected at the store. I checked the customer account in the HQ database, and when I click on receivables, all credits appear as "(pending)" in the list. I don't understand what this means. Note: I closed the billing cycle up to today, and still no change. Help!!!! Think I figured it out... Need a 3...

how to set columns that will stay on screen?
I notice a few Excel files created by others have this feature that the first 2 columns at the left will not move when scroll it horizontally. The scrolling only starts column C. It's great for column or row with labels. How is it set? How to disable it? Thanks, On Apr 8, 5:04=A0pm, liu <spamfree...@yahoo.com> wrote: > I notice a few Excel files created by others have this feature that > the first 2 columns at the left will not move when scroll it > horizontally. The scrolling only starts column C. It's great for > column or row with labels. How is it set? How to di...

No One Knows !!!
I Guess No One Knows Why My Pc Reboots Everytime I Save My Exce Spreadsheet , It Works Ok If I Do "save As -- mickeyhol ----------------------------------------------------------------------- mickeyholt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1483 View this thread: http://www.excelforum.com/showthread.php?threadid=26466 have you tried repairing or reinstalling Excel -- LB7 ----------------------------------------------------------------------- LB79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1215 View this threa...

Only one biweeekly deposit seen by budget setup
Hope someone can help me out here. I get paid biweekly and that's how I have it set up in Money 2002 and I don't have any problems with it. I recently tried to set up a budget and my salary appears under income and it is listed as occurring biweekly but it only used one of the deposits to calculate my total monthly income. Can anyone tell me why it's ignoring one of my biweekly deposits? If I get the details on my salary, it shows the correct monthly amount (approx twice one pay check). At first I thought it was because at the time I was doing this, I had only received one pay che...

RE: Heading Changes as Filtered Data Changes
I need your help!!!!... I have a sheet with more than 5000 records/rows of 150 customers. I applied Filter to filter any selected customer data and take print out. The real nightmare begins when I am asked to take print out of all these customers (or selected) for specific period on separate papers with Name of Cutomer and duration (Start and End date... ex: From 1 Sept 2003 to 31 Dec 2003.) The question is that is there any easy way that after applying filter..... Report heading Changes the name of Customer and pick Start and end date from the Filtered Data. Current head...

How to Write one Log file in Multi Thread program
���� MIME ��ʽ�ľ��кܶಿ����Ϣ�� ------=_NextPart_000_0022_01C33EE2.566EF870 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable How to Write one Log file in Multi Thread program. ------=_NextPart_000_0022_01C33EE2.566EF870 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dgb2312"> <META content=3D"MSHTML 6.00.246...

check one account specifically
I have in my outlook on my work place two accounts: work and private. I have chosen to have my mail checked automatically every 5 minutes (or when I click Send/Recv), which then takes place for both accounts. Actually I would like that only my work mail is checked and not my private mail; I would like to check my private account only on moments I choose separately from checking work mail. I knew how to do this in OL express but I don't manage to get it done in Outlook. I'll appreciate your help. I believe it is located under Tools-->Send/Receive You should see the specific ...

Budget Adjusts when I add a bill
I have recurring payments set up for a monthly expenditure that happens on the first of every month. It just so happens that this month, the expense was $41 dollars over normal (the bill is normally $175). When I wrote the check for $216 and added it in, the Budget Summary adjusted my budgeted amount for this category by the difference. Here is the scenario: $300/ea. month set aside for Pet Care. $175 normally for Horse Board This month it was 216.13 Budget adjusted my $300 to $342 so that I have the normal monthly remainder of $125 left over. How do I correct this so that it goes back...

Help with 2 Pocket PC users syncing to one PC with Outlook 2003
Hi all, I have my PPC all set up and sync with Outlook 2003. My wife is now in the process of having me transfer all of her data oof of her Palm 505 to an iPAQ. She has been syncing to the Palm Desktop, but now I'm thinking that I'm going to have to uninstall Palm Desktop and have her Sync into Outllok on the same computer so that I can get all of her Palm data onto her iPAQ. The problem is that I don't want her data to mix in with mine when I sync her Palm and I want to be able to have her Sync to Outlook (The same Outlook on the same PC) with her own information in the future a...

3 different data ranges to chart on one graph
I have a graph I am trying to create that in essence has three different types of data in it, but they are all associated. I am charting 2000-2005 information. I would have 7 columns of data Year / Spending / %change from prior year / Segments / % change / Project counts / %change from prior year For example this would be what 2001 would look like... 2001/1,500,000/56%/22,000,000/-34%/16,000/84% So my numbers are all over the scale. Can I do this? Any charting advice would be greatly appreciated. Hi Donna - One of the principles of effective data presentation, is to keep it s...

Excel 2007 one to many relationships
I am using version 2007 and have imported about 10,000 records containing account numbers and other various fields. The problem is that from the import I will have the primary record showing the account number and then maybe 4 secondary records below the primary record but with no account number. I would like to find a way to copy the account number to the empty account number fields in the records below the primary. I do not want to have to manually copy & paste from each account number to the empty secondary records below. Is there a formula I could use? Thanks to anyone...

Data to outlook calander
I have a form setup with a command button that when clicked sends information from the form to the outlook calendar. I am using Office 2007. My problem is how to reference a specific calendar other than the default calendar I have the default calendar and another calendar named BidDate. I need to send the information to the BidDate calendar. I am not sure how you reference the BidDate calendar in my Access VBA code "Gary." <gmhull@sbcglobal.net> wrote in message news:%23yjpLteaKHA.808@TK2MSFTNGP02.phx.gbl... >I have a form setup with a command butt...