Alternative Formula For Xl2010

Feb 21, 2010

Sheet2ABCDE1Start Date 01/01/201005/01/2010Holiday211/01/2010Holiday3Days1445the date to be back to work 19/01/2010Excel 2010

Worksheet FormulasCellFormulaB5=WORKDAY.INTL(B1,B3,16,D1:D2)

but how i can get the same result with Xl 2003 or 2007

View 9 Replies


ADVERTISEMENT

Alternative For SUMIF Formula

Jan 27, 2010

The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:

K28 = Last month balance
Column E = Type of expenditure
Column D = Values
Column B = Payments
K23 = Currency conversion rate

View 2 Replies View Related

Formula To Read Alternative Columns - Instead Of =

Jul 14, 2008

I have got several columns of data with subtotals, months, total unit per month, total sales per month, Example

jan feb

total unit total value total unit total value

60 £600 35 £180

SOLUTION

dateunitsale01-Jan6060001-Feb3518001-Mar335801-Apr911220

I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?

View 9 Replies View Related

Any Alternative To INDEX And MATCH Formula

Mar 18, 2009

I have the followings in my spreadsheet.

a) Column A: 900 serial nos of Work Orders. Each is unique.

b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.

Note: Column A & B are the main reference.

c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.

I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.

View 9 Replies View Related

Alternative To Index And Match Array Formula?

Sep 18, 2012

complex formulas in Excel but managed to work out and create the following formula;

{=IFERROR(INDEX('owssvr(1)'!$AE:$AE,MATCH($A9&AK$1,'owssvr(1)'!$C:$C&'owssvr(1)'!$E:$E,0)),"")}

Basically I want to return a call quality score if the name and week match my criteria.

The above formula works perfectly, however, it is very slow and the sheet takes ages to update.

Is there a quicker and more dynamic way to achieve the same result?

View 9 Replies View Related

Alternative To CTRL-SHIFT-ENTER Array Formula

Jan 4, 2009

This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.

Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?

View 4 Replies View Related

Formula Conversion To A Less Processor Hungry Alternative Such As DCOUNTA

Jan 26, 2007

An OzGrid member kindly provided this formula from another post I made here:
http://www.ozgrid.com/forum/showthre...837#post326837

= SUMPRODUCT((--(WEEKDAY(Database!$E$7:$E$3973,2)=(ROW()-3))*1)*(Database!$C$7:$C$3973= 'Pattern Analysis'!$A$4)*(Database!$F$7:$F$3973='Pattern Analysis'!C$3))

My problem is that the =SUMPRODUCT is slowing my sheet down considerably and I was wondering if any of you formula whizzes can convert it to a less processor hungry alternative such as DCOUNTA (if that's possible) or suggest a better method.

View 2 Replies View Related

Alternative To OR

Dec 18, 2009

I use an Array Constant in an IF statement, rather than using OR?

I have tried the following without success:

=IF(A1={0,1},"Something","Something Else")
Which would look like this using OR:

=IF(OR(A1=0,A1=1),"Something","Something Else")
Is it possible? If so, what's the right syntax?

View 9 Replies View Related

Alternative For INDIRECT

Jan 26, 2009

I have used the function INDIRECT in 1 of my files.

The disadvantage is that both files (source and target) have to be open.

Is there a substitute for INDIRECT that works with a closed source file?

View 11 Replies View Related

SUMIFS Alternative

Jun 17, 2009

I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:

View 2 Replies View Related

Alternative To SUMIF

Jan 21, 2010

I'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.

View 2 Replies View Related

IF Limitation - On Alternative

Jul 23, 2009

I have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.

Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...

Zone>Bank>Machine

then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.

Zone>Bank>Machine>Information on that machine

I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...

=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))

View 14 Replies View Related

Exit Sub Alternative

Aug 3, 2009

Exit Sub alternative. I have the following

View 5 Replies View Related

Alternative To A If Statement

Oct 7, 2008

I am trying to make an IF statement and need to enter 12 if's. Excel will only let me go to 7. Example, if Jan is in AE11 the copy the number from B96, if Feb is in AE11 then copy number from c96. Everything works up to July.

View 2 Replies View Related

VB Alternative To If Function?

Jun 4, 2009

I use a spreadsheet to keep track of stock at work.
The invoices are put into rows with the stock code and qty next to each other.

IE:
Invoice No | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty | Stock Code | Qty ect..

I am currently using an If function to check all the cells for an instance of each stock item in a total of four columns.

View 3 Replies View Related

An Alternative To Nested IF's

Sep 19, 2007

I was creating a formula in excel yesterday that used nested IF's but I soon found out that the maximum number you can use is 7. So I did a bit of digging and found out that I would be better of using a Select Case in VB.

What I want to do is this:

=IF((SEARCH("case1",A1)),"text1",IF((SEARCH("case2",A1)),"text2",IF((SEARCH("case3",A1)),"text",...etc until I have listed every possible case and I want to do this for a range of cells A1:A1000 or however many entries I have.

I am assuming I would have to use code similar to the solution in this thread:

[url]

The only thing is I cannot translate this to what I am doing. Do I need to write a loop to go through the entire range of cells I want to check? How do I determine if a specific string is contained within a cell? Once the macro is written do I refer to it from a cell or run it from within the macro menu? Please bear with me as I am new to this and have not coded in VB since school.

View 9 Replies View Related

Alternative To Comments

Jul 30, 2008

For each cell, instead using a comment, I need another way to show a large amount of principaly letter texts, without using hypervincules, and ofcourse comments.

By the way, the same text I enter or modify put in this "way", I need to modify in the MSWord document and viceversa.

I appreciate the linking do not open another app, because the large amount of them I need to work with.

View 9 Replies View Related

Loop Alternative

Sep 5, 2008

Dim myRow As Long
Dim myCol As String
Dim Search As String

Sheets("Master Holdings").Range("H2")

Application.Screenupdating=false
mycol="E"
For myRow=6000 To 1 Step -1
If Activesheet.Cells(myrow, mycol).Value Search Then
Rows(myRow).Delete
End If
Next myRow
Application.ScreenUpdating=True

End Sub

Is there a better way to perform this code?

View 9 Replies View Related

Alternative To An IF Statement

Jun 29, 2009

im trying to figure out how to loop or if there is an alternative to an if statement in this situation here...

in cell b2 i need to say
"if the height =< 12 then return 0
if 15==21 then 1
if 24==36 then 2
if height >=39 then 3"

View 9 Replies View Related

Alternative To Sumproduct...

Oct 9, 2009

I have a sumproduct that evaluates # of conditions and returns the count when everything is met. But with the amount of data I have it is very slow to recalculate. I'm trying to find an alternative. I've read through a bunch of threads about different ways to avoid sumproduct but I can't get them to work. Here is the criteria I'm working with (simplified) .....

View 9 Replies View Related

Alternative To Value Attribute

Feb 2, 2007

I recieved on my last thread (link below), the code provided worked fine.

Previous thread I submitted

I now have a problem with the .value attribute. When comparing Excel finds two cells unequal in '.value' then writes '.values' in changes sheet. When using =A1=B1 the result is TRUE. I don't want equal valued cells to be picked up how can I stop this? (Macro code included in previous thread if needed)

View 9 Replies View Related

Any Alternative To Loop Until

Mar 22, 2007

I have this excel project that captures identification numbers using a scanner. once the scanner gets the info, it will mark the date/time the person was scanned. my problem is once the info scanned is not on the list, it will continue searching till i do an escape or ctrl-break.

heres the

Private Sub cmdOK_Click()
ActiveWorkbook. Sheets("list 2007").Activate
Range("F25").Select
Do
If ActiveCell.Value <> txtID.Value Then

ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = txtID.Value
ActiveCell.Offset(0, 2).Value = Now()
Range("F25").Select
txtID.Value = ""
txtID.SetFocus
End Sub

View 7 Replies View Related

INDEX As An Alternative For INDIRECT

Feb 18, 2009

In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.

It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.

However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function

This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.

View 7 Replies View Related

Alternative To Pivot Table

Jun 22, 2009

I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same.

In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..

View 14 Replies View Related

IF Command - Too Many Levels, Alternative?

Sep 15, 2009

I am trying to use the command below to report what type of bonus is to be awarded. .05% - 1% bonus, commission based.

=IF(E7>=0.12,1,IF(AND(E7>=0.115,D11<=0.1199),".09",IF(AND(E7>=0.11,E7<=0.1149),".08",IF(AND(E7>=0.10 5,E7<=0.109),0.07,IF(AND(E7>=0.10,E7<=0.1049),"0.06",IF(AND(E7>=.095,E7<=.099),".5",IF(AND(E7>=.09,E 7<=.0949),".04",IF(AND(E7>=.085,E7<=.0899),".03",IF(AND(E7>=.08,E7<=.08499),.025, IF(AND(E7>=.07,E7<=.0799),".15",IF(AND(E7>=.065,E7<=.0699),".005")))))))

It is telling me there are too many levels. I am not an Excel expert, so I am trying to figure out an alternative to this command, I am sure one is available.

View 5 Replies View Related

Alternative To Lengthy OR Statement?

Dec 30, 2009

Is there a more efficient method to what i have attached? i.e. if E4=anything between B2 and B10 then C2 is displayed. Between B11 and B16 then C11 is displayed.

View 4 Replies View Related

Alternative For Paste Special

Jul 9, 2009

During the execution of some code I come to a point where the properties of a range of cells needs to be converted to numeric (is at that point text).
Normally I do that by adding a 1 in an cell nearby and use the copy/paste special method.

I was wondering if I can do that in a different way.
Is it possible to define a MultiplyFactor as 1, and use this in paste special part.

Something like: .....

View 9 Replies View Related

Alternative To Multiple IF THEN Statments

Jun 30, 2009

Does the following formula make sense and if not, what do I need to change? I'm receiving a "#VALUE" error. =MROUND(IF(A9=1,SUM(D1*0.1), 0) (IF(A9=2, SUM(D1*0.1/2), 0)), (IF(A9=3, SUM(D1*0.1/3), 0)), (IF(A9=4, SUM(D1*0.1/4), 0)),6)

It is possible to have multiple IF,THEN statements in one formula? A9 and D1 are the variables driving the formula. Let me know if I need to provide a detailed explanation.

View 4 Replies View Related

Alternative To Summing Nested If's

Oct 8, 2009

I have a table of approx 20k customers (column A) and their $ sales (column B)for a time period. I need to sum the sales of approx 50 customers. I have the names of the 50 customers to be summed in one column so i can reference them and easily change out a customer if need be.

View 5 Replies View Related

Macro With Alternative Option

Oct 1, 2008

Just a curios question is it possible to have a button on a macro that if you press it would run one set of instruction, but if the user was holding a key down say the shift key and pressed the same button, it would run a second set of instruction

EG
If [Shift] key active then
Run command 1
else
Run command 2.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved