Here's an example of what I need...
Cell Value
_________________
D3 100.0000 <---------------- Random number entered
D7 37.6875
D8 75.3750
<---------------- 100.0000 falls
D9 113.0625 in between here.
D10 150.75000
D11 188.4375
D12 226.1250
D19 _________ <---------------- Target number
I need excel to recognize where the random number ( D3 ) falls on the
scale ( D7 - D12). Then, I need excel to know whether to round up, or
round down, and automatically place the value in cell D19. FYI,
numbers on the scale are in increments of 37.6875.
In the example, 100.0000 falls in between 75.3750 and 113.0625, and
should then be rounded UP because 100.0000 is more than way
between 75.3750 and 113.0625. So the target number would be 113.0625.
I need a formula that will help me achieve this. Any help would be
greatly appreciated.
* Need serious help with formula/function *
5 posts
• Page 1 of 1
- MS SQL SERVER
- sharepoint
- IIS
- Windows XP
- internet explorer
- dotnet-framework
- WINDOWS VISTA
- Windows XP
- Visual Studio
- Win32 Programming
Jump to:
- 1. multiple copies of XL
Excel 2002 SP3 Win XP HE SP1 hi, when i click on an xls file in windows explorer it opens the file in excel; however, if i am looking for another xls file and repeat same procedure, it opens the xls file in a *separate* instance of excel. on and on so i can find myself s'times with up to 5 or 6 multiple instances of excel all running concurrently. is there a way to prevent this? i.e. make all xls files opened or clicked-on in windows explorer open in the same instance of excel--barring opening them up all at once but sequentially in time, as needed? tx. - 2. Force English formatting
I have an application used by both English and European clients. This is significant because often when European clients are entering Data into the TextBoxes, they will accidentally enter it in the format that they are used to: 1,000.00 in EU formatting = 1.000,00. This of course screws up my whole application. How should I go about safe-guarding this from happening? - 3. IsNull and a named range
I have a one-cell-named range called REQT and if there is a value inserted, I want to go to a worksheet called RFP. The variable BeenThereAlready ensures this happens only once. I made this one cell a named range so that any modifications referring a specific cell would not require a change in code. What I have below is not working and the solution is simple but I have hit a wall. Any suggestions appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If IsNull((Range("REQT").Value)) = False And BeenThereAlready = False Then Sheets("RFP").Activate Sheets("RFP").Range("A1").Select BeenThereAlready = True End If If IsNull((Range("REQT").Value)) = True And BeenThereAlready = True Then BeenThereAlready = False End If End Sub - 4. Matching within results of a match
Table 1 (Purchases): Column A has a list of part numbers. Column B has a list of purchase dates. Column C has a list of material costs unique to the part number and part ID. Table 2 (Sales): Column A has part numbers Column B has sale dates Column C - needs to have the purchase cost from the most recent purchase of this part I'd like to look up the ID in table 1 column A, then find the purchase date closest to but before my sale date, and return the purchase cost from table 1 column C to table 2 column C. I know how to do array-entered sum(if..) statements, but I only know how to get exact matches, not closest matches. And I know how to do index(match...), but I can't figure out how to do two matches inside one index function. Thanks.
Next
* Need serious help with formula/function *
by The Flea Circus » Wed, 07 Mar 2007 10:23:49 GMT
Re: * Need serious help with formula/function *
by Harlan Grove » Wed, 07 Mar 2007 10:49:32 GMT
The Flea Circus <> wrote... ... ... If your tables is really graduated by constant 37.6875 increments, D19: =ROUND(D3/37.6875,0)*37.6875 More generally, use the array formula D19: =INDEX(D7:D12,MATCH(MIN((D7:D12-D3)^2),(D7:D12-D3)^2,0))
Re: * Need serious help with formula/function *
by The Flea Circus » Thu, 08 Mar 2007 07:41:17 GMT
On 5 Mar 2007 17:49:32 -0800, "Harlan Grove" < XXXX@XXXXX.COM > Thanks! The first formula worked! Second formula didn't, but who cares? Thanks again.
Re: * Need serious help with formula/function *
by Harlan Grove » Thu, 08 Mar 2007 10:06:59 GMT
The Flea Circus <> wrote... ... [2nd formula] I did mention that the second formula was an array formula. Type the formula, then hold down [Shift] and [Ctrl] keys before pressing [Enter].
Re: * Need serious help with formula/function *
by The Flea Circus » Sat, 10 Mar 2007 07:59:55 GMT
On 6 Mar 2007 17:06:59 -0800, "Harlan Grove" < XXXX@XXXXX.COM > You're right. My apologies. The second formula worked great too. thanks again.
Similar Threads:
1.I have a complicated formula that I need SERIOUS help with ple
And the criteria for shading it is_________________? -- Don Guillett SalesAid Software XXXX@XXXXX.COM "Thrlckr" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ... > It is shaded manually. > > "Don Guillett" wrote: > >> What and how is the cell shaded? Manual, conditional formatting, macro? >> >> -- >> Don Guillett >> SalesAid Software >> XXXX@XXXXX.COM >> "Thrlckr" < XXXX@XXXXX.COM > wrote in message >> news: XXXX@XXXXX.COM ... >> > Hi, >> > >> > I am trying to count cells in a row that either contain a "1" or are >> > "blank," but some of the cells in the row are shaded. The problem that >> > I >> > am >> > having is that I cannot make the formula work where it counts and adds >> > up >> > all >> > the "1's" and "blank" cells, but not the "shaded" cells. The other >> > problem >> > is that sometimes the shaded cells do contain a "1" in it, but I still >> > do >> > not >> > want that cell counted. >> > >> > If someone can PLEASE help me with this, I would greatly appreciate it! >> > >> > Sincerely, >> > Carol >> >> >>
2.Serious help needed for this project Can anyone help me with this
I have a w/s that compares forecasts and actuals. Column A is Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov Dec. Column B is the Planned Forecast in $ for each month. Column C is the Actual Amt in $ that was spent for that month I need 3 formulas that at the end of each column do the following: 1) YTD total for each - this formula would take the $ amount for each month and * by the number of days in that month then divide by the actual number of toal days YTD so far. We are a month behind so we would only use through July not include August yet. In other words it is not to date (system date). 2) Forecast total - $ amt for each month * # of days in the month divided by 365 days for the entire year. 3) Sum for the year (got this one figured out). for Actual Totals: Same as for forecast for YTD. The second formula is my problem: for Actual $ amt MTD Forecast. Take the $ amt for each month *the # of days in that month PLUS include the $amt for the forecast months that we have NOT gotten to yet this year. We are done with July, so you would only include Planned/Forecast $ for Aug, Sept, Oct, Nov, Dec taken from that column and sub for actual $ until we can replace it with the info when it comes in. This total is under Actual/Forecast. The last one for Actuals is sum and I got that one. 3) Total - got this one - it is the actual sum YTD. EXAMPLE Month Forecast Actual Jan 91.21 51.75 Feb 82.39 109.31 Mar 91.21 159.24 Apr 88.27 69.86 May 91.21 51.92 Jun 89.69 53.76 Jul 92.73 51.20 Aug 92.73 Sep 89.73 Oct 93.25 Nov 90.26 Dec 104.22 _______________________________ YTD 89.64 77.86 Forecast 91.50 84.67 Total 1096.90 547.05 Jannie
3.Serious help needed for this project Can anyone help me with t
Is the data in column A actual dates, with cell format of "mmm": I have assumed they are dates so "Jan" would be 01/01/2007, "Feb" 01/02/07 (UK format!) "Value" indicates a data mismatch and the dates most likely candidate. Did the days of the month work OK in column D? I tested my solution on your data and got identical results. If you still have problems post w/book to: toppers <at> NOSPAMjohntopley.fsnet.co.uk Remove NOSPAM "Jannie worksheet function" wrote: > I understand what you are saying here to do but I copy and paste the formulas > but I get a !Value error. > > "Jannie worksheet function" wrote: > > > I have a w/s that compares forecasts and actuals. > > Column A is Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov Dec. > > Column B is the Planned Forecast in $ for each month. > > Column C is the Actual Amt in $ that was spent for that month > > > > I need 3 formulas that at the end of each column do the following: > > > > 1) YTD total for each - this formula would take the $ amount for each month > > and * by the number of days in that month then divide by the actual number of > > toal days YTD so far. We are a month behind so we would only use through July > > not include August yet. In other words it is not to date (system date). > > > > 2) Forecast total - $ amt for each month * # of days in the month divided > > by 365 days for the entire year. > > > > 3) Sum for the year (got this one figured out). > > > > for Actual Totals: > > > > Same as for forecast for YTD. > > > > The second formula is my problem: for Actual $ amt MTD Forecast. Take the $ > > amt for each month *the # of days in that month PLUS include the $amt for > > the forecast months that we have NOT gotten to yet this year. We are done > > with July, so you would only include Planned/Forecast $ for Aug, Sept, Oct, > > Nov, Dec taken from that column and sub for actual $ until we can replace it > > with the info when it comes in. This total is under Actual/Forecast. > > > > The last one for Actuals is sum and I got that one. > > > > 3) Total - got this one - it is the actual sum YTD. > > EXAMPLE > > Month Forecast Actual > > Jan 91.21 51.75 > > Feb 82.39 109.31 > > Mar 91.21 159.24 > > Apr 88.27 69.86 > > May 91.21 51.92 > > Jun 89.69 53.76 > > Jul 92.73 51.20 > > Aug 92.73 > > Sep 89.73 > > Oct 93.25 > > Nov 90.26 > > Dec 104.22 > > _______________________________ > > YTD 89.64 77.86 > > Forecast 91.50 84.67 > > Total 1096.90 547.05 > > > > Jannie > > > > > >
Getting a "Not Enough Memory" error when I attempt to open a spreadsheet with only "OK" as an option to clear the dialog box. I have a lot of data in this speadsheet. It is only data in fields, no calculations, no charts/graphics and it is only <3 mb in size. It resides on a network drive so various users can view it but only one user has 'write' rights. It isn't open by another user. We get the error on any PC we try to open it on. Previous posts did not provide any response but I am in need of help here and would appricate any assistance I can get to get the data back. Excel did not give a warning about not being able to get your data at a certain point. I hope someone can help me....Thanks!!!!
6. I NEED SOME SERIOUS HELP =)
8. Serious Need of Help Here - retrieving data from excel to use as labels for toolbar
5 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 90 guest