Multiply The Sallary To One Of The 3 Variable Depending On The Category

May 30, 2008

I'm trying to use IF with other variable but is not working.

What I have:

Colum G12 - NET Sallary
Colum I12 - 3 possibilities (MRM, ME, AB)
Colum J12 where I will have my formula.

I have another table with 3 datas:

MRM = 1,709 (P3)
ME = 1,544 (P4)
AB = 1,630 (P5)

What I need to do is the following:

J12 = IF I12 = "MRM" THAN G12*P3; IF I12 = "ME" THAN G12*P4; IF I12 = "AB" THAN G12*P5

I need to multiply the sallary to one of the 3 variable depending on the category: MRM, ME and AB.

View 9 Replies


ADVERTISEMENT

Changing Results Depending On The Category?

Jun 18, 2014

I'm trying to figure a function wherein if I change the category, the results will automatically changes. see attached sheet.

View 6 Replies View Related

Formula To Multiply Total Number Per Category With The % Of Total?

Dec 13, 2013

number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.

View 2 Replies View Related

Simple Multiply VBA Variable To Range

Jan 5, 2010

how do I multiply a variable to a range?

I've tried the following code but that does not work. My variable name is CurrRate

with range("D7:D207, G7:G207")
.value = .value * CurrRate
end with

View 9 Replies View Related

Excel 2010 :: Group Table By Category And Put Category In Its Own Row?

Sep 18, 2013

Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).

Category
Genre
Title
Author
Year

[Code]....

View 2 Replies View Related

Excel 2000 - If A1 Equal Or Less Than 1000 Multiply By B1 Otherwise Multiply By C1

Oct 30, 2013

Excel (2000), what formula would I use to accomplish the following?

If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.

View 9 Replies View Related

How To Select Variable Row Depending On Number Introduced

Jan 24, 2013

I want to introduce a number in the textbox1, and that number sets which entire row to select for making a chart. i thought of something like this, but i cant put the variable x just like that.

Private Sub CommandButton1_Click()
Dim x As String

If TextBox1.Text <> "" Then
x = TextBox1.Text
Range("Ax:Mx").EntireRow.Select

[Code] .....

View 12 Replies View Related

Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell

Mar 16, 2009

Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.

Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?

The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.

View 4 Replies View Related

Move To A Cell Depending On Variable Active Cell

Jul 23, 2013

So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.

Here's what I got 'til now:

Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Select

[Code] ........

View 3 Replies View Related

Insert Formula Depending On Cells Depending Of Variables?

Jan 27, 2014

In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:

[Code] .....

All my variables are declared:

Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed

The error message I receive is the following: "Run-time error 13, Type mismatch"

I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.

View 3 Replies View Related

Sum Of Top 5 Category Wise?

May 8, 2013

I need a sum of top 5 numbers category wise. I have attached a sheet in which I would be requiring Sum of Top 5 for the month of May. I have shaded cell where i would be requiring a formula...

Also I may want to include other category as well.. For e.g. sum of top 5 for the year 2011 as well... I have tried every thing i could from Sumif to sumproduct to Large...

View 9 Replies View Related

Average By Category

Nov 16, 2008

The title isn't very good I know; I don't know the right terminology to explain the problem properly, but i'll give it a go: In column A I have a list of names, all of them appear multiple times. In column B there is a corresponding percentage. I would like to create a formula that takes the average of all the percentages that have the same corresponding name, or 'category'. I don't want to just go through and do it manually since there are roughly 32,000 different values.

I did have one idea; in column C I pasted in a list of all the possible names in column A and then in column D I used countif to find out how many times each name recurred. If both columns A and C are in alphabetical order, I thought I could use the normal average function, by doing, for example AVERAGE(B1:BD1), but this didn't work. Is there some special way in which you can use a cell name (i.e. D1) instead of a number in a cell range? I tried brackets, speech marks etc. but nothing worked. I have searched around a bit on this forum to see if my question had already been answered, and did some extensive googling, but I couldn't find a solution.

View 3 Replies View Related

Check If ID Is New Then Sum Per Category

Aug 30, 2007

I have 4 columns:

id (A), number (B), category (C) and month (D)

I think this could be done using VBA or formula's,
I understand how to SUM up based on criteria, but I
haven't a clue where to start to see if something was
present/ not present in the previous month and category and vice versa

If I decide a month to look at in E1, say February.. then i need
to calculate 4 things:

1) was an id in February and not in January (for this category) if so then sum up (B) per category (C) for February

2) was an id in January and not in February (for this category) if so then sum up (B) per category (C) for January

3)was an id in February and not in January (not in any cateogry in jan) if so then sum up (B) per category (C) for February

4)was an id in January and not in February (not in any category in feb) if so then sum up (B) per category (C) for January

I need to do this for each category (C)

My data would be something like this (I cant download addon from this PC, i have it at home)

id____number____category____month
1_____10_________A___________january
2_____20_________A___________january
3_____30_________B___________january
4_____40_________B___________january
1_____50_________A___________february
2_____60_________A___________february
3_____70_________A___________february
5_____80_________A___________february

so if I chose to put February in E1,

I would need the results something like follows:

category__new to category__left category__n__left totally
A___________(70+80)__________none____________(80)________none
B___________none____________(30+40)__________none________(40)

View 9 Replies View Related

Sum Based On Category

Jun 15, 2007

Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.

Have attached a sample xls..

View 5 Replies View Related

Count Items Of Only One Category?

May 5, 2014

I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.

View 14 Replies View Related

Listing Data By Category

Jul 25, 2014

I have created a simple account list with - date - detail - category - amount.

How can I automatically list each transaction in date order by category on other pages. Each category is represented by a letter - e.g. 'T' for travel. In this example I want to list all travel transactions in a separate list in date order on another page for easy printing.

I have made a Summary page for each category but cannot work out how to make a detailed list for each category.

View 13 Replies View Related

Sum Data By Category And Date

Dec 23, 2008

I need a formula to count list items if the date is between a specified parameter. I am trying to count how many help tickets were logged for a particular group or bureau during a specific week.

How many AA's between 12/1/2008 and 12/8/2008?

Bureau Date Opened
AA12/1/2008
AA12/1/2008
AC12/2/2008
AA12/6/2008
AB12/12/2008
AE12/15/2008
AC12/16/2008
AD12/17/2008.........................

I know how to count a list of items when you identify what you want it to count (I11 (Access)) and where (Application (Named Range)) but I can't figure out how to include the date parameter.

I J
Access23 =COUNTIF(Application,I11)
Acrobat2
ADMIN4

View 4 Replies View Related

Summing Each Category Per Month

Jul 27, 2009

is there a formula I could use to total for each month all of the e.g. C3303, for each of these categories as detailed in the table at the bottom

View 4 Replies View Related

Change Model To Category?

Apr 13, 2014

1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?

I have this code attached, but only works if I have the word Phaser or WorkCentre.

View 8 Replies View Related

Count By Category And Insert New Row

Mar 1, 2009

I have been strugling this for the last 3-4 days but could not resolve it. I have list of items and i would like to count those items by item type per the user's selection.

For instance, the user selects either Yes or No and i would like to count only the ones that the user set for Yes only.

View 3 Replies View Related

Return Top 10 Entries For Each Category

Oct 29, 2009

I have a product database that contains in column A the product name (eg "Granny Smith"), in column B the category (eg "Apple") and in column C the number of sales (eg 10,123).

I was looking to use code to list the top 10 products for each category, so to show the top 10 names I would enter in something like...

View 10 Replies View Related

Find The Top Result For Each Category

Oct 23, 2007

I want to create a Pivot table, and is now working on the data sheet. In this, every salesman have one row each day containing their sales result that day.
I have data in 3 columns. In column A the date, column B contains the Name of the salesman and column C the sales result that day. Now I want to update column D with each salesman's best result. I want each row to contain the top result for that particular salesman.

Date | Name | Result | Top result
1/10 | Pete I | 11.111 |
2/10 | Pete I | 19.100 |
3/10 | Pete I | 23.408 |
4/10 | Pete I | 16.774 |
1/10 | Sara G | 10.884 |
2/10 | Sara G | 10.739 |
3/10 | Sara G | 36.469 |...................

View 3 Replies View Related

Condensing A List For Each Category

May 27, 2008

In sort of a follow up to the Condensing A List Thread
[url]

I would like to condense the list into a series of list for each category.

The example below is a synoptic view with some data and does not include the full table.

I would like to do it with functions, i can do it with vb, at this point, I'm more on a mission to do it without it.

View 9 Replies View Related

Placing Into The Correct Name And Category

Jul 16, 2009

i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:

Table 1 - Items with quantity that is waiting for input:

Quantity
Product
5
Button A White
7
Ribbon B Blue
8
Thread A Black
10
Cloth A White
3
Button B Blue
4Button C White
9Ribbon A Pink15Button A White
Above is just a part of it, the list goes on to over 200 rows long.

Table 2 - Existing database of all products:

Products
Quantity

View 9 Replies View Related

Summarize Table By Category

Jun 4, 2007

I have an Excel sheet which has Category Column, I have set an auto filter for it, When I click a particular Category data pertaining to that Category is visible.

But my problem is I need help (VBA), that loops category autofilter, through each Category item and capture( sum number of items in that particular category) and put it in an other excel sheet. Kindly Help.

View 9 Replies View Related

Inserting Category Beside Products

Jun 5, 2007

I have an Excel Sheet which has the Products column. I need to insert Category beside every products.

View 7 Replies View Related

Search Value And Return An Associated Category Name

Apr 13, 2008

I'm attaching a spreadsheet in order for you to understand my question, and I will explain it based on that, ok? I apologize in advance for english mistakes, since it's not my 1st language. I want to search the salary value in column B and, in Column C, I need the job title to be posted based on what is written in Column J.

The table h2:i8 contains the maximum salary a person on that position can earn. So when you look the salary on column B, it should seach in column H and compare to the maximum value, and return the description on Column I that corresponds to that salary range. So if somebody earns 6500, it should be qualified as a supervisor, because its salary maximum is 7100. I have searched the forum here and found similar problems, with suggestions to use vlooup and Index. But I just can not figure it out.

View 2 Replies View Related

Horizontal Category Axis Label?

Mar 11, 2014

I have a chart and a number of the data sets have a horizontal data but then some sets don't. How can I add the same horizontal data to the sets that don't?

View 1 Replies View Related

Displaying Data By Category By Year

May 1, 2014

DATA: list of films released by year, with box office totals and genre

GOAL: Function to sort data by year, then displaying how many films in each genre were released each year. E.g. horror comedies, romantic films released in 1981, 82, etc

Is there a function, or series of functions, by which I can do this without having to sort through manually?

View 3 Replies View Related

Splitting Frequency Data By A Category

May 21, 2014

It may be easier to view the attachment to see what I am trying to do.

I would like to split frequency data by groups.

So I have 2 different names in column A, with a bunch of their ratings in column C.

I would like to be able to view the frequency of their individual ratings to see who gave the most types of ratings.

View 3 Replies View Related







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