SumProduct Fast Solution

Jan 30, 2010

my questions below are more oriented to hows & whys, as opposed to asking for a fast solution to a pressing problem.

I'm just reviewing the basics of SumProduct to be sure that I understand what I'm really doing when I deploy the function. So, I have the following formula:

View 3 Replies


ADVERTISEMENT

Convert 'FAST: 34' To 34

Jun 14, 2006

how to custom the column with data FAST: 34 to 34 and similar stuffs in the column to numbers. Theres a space in front of the number.

View 2 Replies View Related

Fast Typing With Keywords

Mar 1, 2007

I'm working on an Excel spreadsheet and basicly i'm inserting manually data from a lot of paper forms.

It isn't a dificult thing, but is boring, because I have to do more than 2000 forms per week!
I want to accelerate, and how to do that

Problem: "Address"! The Excel page that i'm working has a column to feel with Addresses manually, but I have a table with all correct Address names from the city that I'm working about in other page.

What I would like is when start typing some keyword from the Address cell, it could show me all the choises of input (based on the correct Address Table)

Excel, by default, show, by exact order, all the choises, already typed on the same collumn.
I don't want that.

- imagine I have the Table 1 (representing the table with all city adresses) with a collumn called 'Address' with:

A1 - Street ZXY
A2 - Avenue QWE
A3 - Street QWE
A4 - Avenue ZXY

when typing on another Table, if I write something like “QWE” show me the A2 and A3 to choose wich I'm going to feel, and the same to “ZXY”, showing the A1 and A4. Other thing, if I write “QWE street” may show A3 also. Keyword is what i'm talking about

View 9 Replies View Related

Fast Way To Clear A Range Of Cells

Jul 8, 2009

This seems to take much longer than I would have expected. Is there anything I can do to speed it up, or is this just something to live with?

View 13 Replies View Related

Fast Macro To Hide Empty Rows

Mar 22, 2014

Im looking for a macro that hide empty rows. I found some simple macro but that are long to execute. While looking for a faster code, I found two codes that work pretty fast. But as I don't understand VBA I am not able to adjust them to my situation.

First macro: I am able to specify my range (B6:B77), but the macro applies to blank cells and I need to apply to "" cells.

[Code] .....

Second macro: very fast as well. Here, it applies to "" cells, but I am unable to specify a range. So the rows 1 to 4, which are empty, are hidden but should not.

[Code] .....

View 11 Replies View Related

VBA / How To Spin Chart Very Fast Then Slow And Stop

Nov 20, 2013

I want to make a prize wheel (aka Wheel of fortune) I try to follow this code >> wheel of fortune but I stuck at the animation in order to make it look like the real prize wheel. as far as I google, this youtube (Simple Excel Pie Chart turned into AMAZING Electronic Prize Wheel of Fortune! - YouTube) demonstrate the result that I really want my chart would be.

how to make the prize wheel spin very fast at first and slower later until stop?

View 3 Replies View Related

Monitor Fast Moving Decimal Values In The Even Numbered Cells

Jul 26, 2009

To monitor fast moving decimal values in the even numbered cells( e.g. F2 to F 30 ) of a column, ignoring blank cells, and if two of cells values simultaneously fall below a certain value e.g. 2.04 True if not False.

View 9 Replies View Related

Having A Solution Returned As A Value

Dec 9, 2009

Is it possible to have a formula that will return the answer as the value.

For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.

I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.

Would this require a macro?

View 5 Replies View Related

Unique Job #'s By Date VBA Solution

Jan 20, 2009

I'm looking for a VBA solution that will return a list of unqiue job numbers in B by date, the below is some sample data in Sheet 1

Sheet 2 is the result I'm looking for and usually there will be muliple job mumbers for the same date and in this case I'm fine to leave it as a blank cell in sheet 2 for that day.

Unable to use advanved filtering as it's a data table thats changing all the time. It's verly likely that the same job numbers will be in multiple days....

View 11 Replies View Related

VBA Solution - Inserting Machine Name

Apr 23, 2009

I'm looking for a VBA solution that will look at the machine name above in blue and insert in in column E

Machine 2's expected results have been filled in....

View 9 Replies View Related

Provide Both Formula And Vba Solution.

Jan 24, 2010

I have an excel sheet which has been exported from an 'Employee Attendance Software',

http://www.flickr.com/photos/34247990@N06/4299288997/

In the above screenshot, The employee name highlighted in Yellow should feauture in column A in the cells highlighted in Yellow.

Similarly, there are some other employees in the list for whom I have to populate the data in a similar fashion

http://www.flickr.com/photos/34247990@N06/4299288959/

View 9 Replies View Related

Use Lookup Formula For Solution

Feb 7, 2007

I am trying to solve a problem with = LOOKUP. Here's the data. A1 can have a value between 1-100.

IF A1 IS <80.00, NO MULTIPLICATION IS NEEDED
IF AI IS 80.01 TO 85.00, MULTIPLY A2 BY 0.37%
IF AI IS 85.01 TO 90.00, MULTIPLY A2 BY 0.52%
IF AI IS 95.01 TO 95.00, MULTIPLY A2 BY 0.87%
IF AI IS 95.01 TO 100.00, MULTIPLY A2 BY 1.07%

Is there a =LOOKUP formula that can be used here?

View 2 Replies View Related

IF Input And Color Identify Solution

Oct 9, 2008

I have the new version of excel and I can't figure the correct formula to do the following.

Attached is my example.

A1 is available Inventory qty. C1:H1 are forcast qty's. I would like to have the available forcast qty. shown in black and the unavailable forcast qty. shown in red based on available inventory shown in A1

Example
So C1,D1 and E1 would be Black and F1 and G1 would be red. As inventory changes so would the colored numbers on the forcast.

View 14 Replies View Related

Hlookup With Dates Solution Needed

Oct 3, 2008

I am using HLOOKUP to show a date depending on the month shown in B10.

If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".

Here is my formula:

=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.

View 9 Replies View Related

Returning A Solution, Of A Formula, To A Different Cell

Mar 3, 2010

I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.

View 9 Replies View Related

Multi-dimensional Array Solution

Jan 12, 2007

I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level:

I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red).

Here is my existing Keyword highlighting
Sub Highlight_Keywords()

Dim vntWords As Variant
Dim lngIndex As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim lngPos As Long

View 9 Replies View Related

Text Overwrites Blank Cells! Solution?

Jan 28, 2009

My problem is that long text for the cell overlaps any blank cells to the right. I have more then 20.000 rows, so I would not like to put a space character in all the blank cells. Is there a way of formatting which will stop this? I don't want these texts to overlap the blank cells!

View 3 Replies View Related

Conditional Formatting Is Not Resulting Of Desire Solution

Dec 29, 2009

I have the attached table. I need to format with conditional formatting with the following criteria:

1) If >= 80% , then flag as green
2) If >=1% but <80%, then flag as yellow
3) If 0%, then flag as red.

I did the formatting in conditional formatting but the it did not results in the desired solution.

View 4 Replies View Related

Extending Previous 'conditional Statements Solution'

Jan 30, 2010

use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution:

http://www.excelforum.com/excel-gene...-new-post.html

I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.

View 2 Replies View Related

Desperately Seeking Solution - Payroll Tax Withholding

Feb 1, 2007

I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is
Over.................But NOT Over............Then Subtract.........And multiply
$0.00...............$195.00.....................$5 1.......................10%
$195.00............$645.00.....................$99 .......................15%
$645.00............$1,482.00..................$351 ......................20%
$1,482.00..........$3,131.00..................$447 ......................25%
$3,131.00..........$6,763.00..................$849 .....................30%

If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10

View 9 Replies View Related

VBA Solution To Compare/replace Data Between Columns

Jul 4, 2009

I have two columns of adjacent data Call them A & B (although this could differ)

If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.

View 9 Replies View Related

Solution/altenative To Data Table Using >2 Variables

Feb 9, 2010

Is there a way to test out a formula using Excel that has more than 2 variables? (If only 2 variables, I would use a simple data table). The formula I am testing has 50X50X20 variables (and as a subset, each variable has 2500 lines of assumptions!). I don't need to see all the results, only the result that produces the highest result. For simplicity (if someone has an answer) let's assume 3 variables are A1, A2 and A3. Formula is in A4 and formula is A1+A2+A3. Assuming A1 can be 1 to 5 and A2 same , A3 same, the long hand calc is for me to run a data table using variables in A1 and A2 the fixing A3 (as 1)..note maximum result....then run data table again using variables in A1 and A2 and changing fixed value in A3 to the number 2...and so on and so on....in this simple example I know the max result would be 15, but much more difficult in my spreadsheet test.

View 9 Replies View Related

Finding Effective Solution For Updating Links

Apr 4, 2006

I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???

View 7 Replies View Related

Multiple Auto Filters Set, Is There A Fast Way To Set Them All Back To "(All)"

Apr 6, 2008

I wasnt sure if I could make a shortcut on the screen, such as a button that says "Reset all filters". I need to make it very easy for non-savy excel users, and would like to stray away from using any keyboard shortcuts, that will Be hard for them to remember, but I will use this if absolutely necessary.

View 14 Replies View Related

Remove Data From Custom For Fast And Keep The Needed Data?

Apr 7, 2014

We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.

View 4 Replies View Related

Looking For A Solution On How To Convert A Returned Cell Address To A Cell Reference

Dec 2, 2009

I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.

View 2 Replies View Related

Macro Runs Fast When Rows Added / Deleted Slow When No Rows Added / Deleted

Jun 27, 2013

We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.

Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.

The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.

The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.

We can't figure out why the macro takes longer to run when no changes have been made?

View 8 Replies View Related

I Want To Be Able To Take The Solution To The Formula And "fit It" Into Another Formula.

Jun 16, 2009

=IF(AC4509="","",IF(W4509=1,ROUND(AR5+(0*(9.693/C4509)),1),ROUND(AR5+(X4509*(9.693/C4509)),1)))

If the value of W4509 is equal to 1, the solution would be whatever is dictated, otherwise, it uses the alternative formula to achieve the answer.

My question: I want to be able to take the solution to the formula and "fit it" into another formula.

Example: ((the "If W4509=1" formula)-88)*(C4509*.09693)+100-0) and ((2nd formula)-88)*(C4509*.09693)+100-X4509)

View 9 Replies View Related

Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and

SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and

SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

View 10 Replies View Related

Sumproduct..?

Jul 11, 2009

I have the following formula:

View 4 Replies View Related







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