excel,if-statement,excel-formula,excel-2007 , Multiply values based on numerous criteria


Multiply values based on numerous criteria

Question:

Tag: excel,if-statement,excel-formula,excel-2007

I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem:

I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables

I have to find the "sell price" in the first table. In order to do this I need to match the "type". After that, I to check if the "cost" is more than column B in the second table.

Then I'd find the multiplier for the corresponding cost. Once I find the right multiplier, then I can find the "Sell Price". The "Sell Price" equals the "Cost" x "Multiplier".

So I need a formula for Column D that matches the type, then finds the biggest possible "If Cost is more than", and finally multiplies the corresponding multiplier with the cost.

For example, let's say I have item "467874". It has the type: "PKG" and a cost of $8.45. Because it's more than $3.00 but less than $10.00, I would use the multiplier 1.85

So the Sell Price for 467874 would be 8.45 x 1.85 = $15.63

I'm sorry if I didn't explain this well enough. If you can find a solution I would be very grateful.

EDIT: I need to be able to change Sheet2 later, so the formula would have to be dynamic. Otherwise I would just use "IF" statements. So I want to be able to add and delete fields onto Sheet2.


Answer:

With a changed lookup array in Sheet2:

SO30652011 example

I suggest:

=ROUND(C2*IF(B2="PKG",INDEX(Sheet2!C$2:C$7,MATCH(C2,Sheet2!B$2:B$7,1)),IF(B2="POPULAR",INDEX(Sheet2!C$8:C$11,MATCH(C2,Sheet2!B$8:B$11,1)),INDEX(Sheet2!C$12:C$14,MATCH(C2,Sheet2!B$12:B$14,1)))),2)  

in D2 copied down to suit.


Related:


Python - Using a created list as a parameter


python,list,loops,if-statement,compare
When I run my code it tells me: Type Error: unorderable types: str() < float(). I can't figure out why it won't let me compare these two numbers. The list I am using is defined, and the numbers in it have been redefined as floats, so I'm not sure what...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In


excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

If statement for search field in Rails


jquery,ruby-on-rails,search,if-statement
I have a blog site and recently added a search bar for visitors to browse through blog posts. When used, all of the posts go away in the directory and only those which return via the search appear. In order to show the general directory and view all, I would...

Converting column from military time to standard time


r,excel
I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

python if statement inside for syntax error


python,if-statement,syntax-error
I wrote some code but I can't get why the pyscripter is telling a syntax error in the if statement: #search for 9 elements file_writer = open('C:\\PythonProject2\\commands_NUM.txt','w') for item in data_indices: flag= search_object(item,data,obj_value_min,obj_value_max) if flag = True:###ERROR file_writer.write('frame0: ' + str(item[0]+1)+' ' + str(item[1]+1) + '\n') ##Here is the syntax...

Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups


excel,vba,excel-vba
I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on...

Copying sheet to last row of a sheet from another workbook


excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

The if statement doesn't work for false in php


php,if-statement
When trying to get familiar with if statement in PHP, this happened. First time i tried this code below. if(true) {echo 'true';} else {echo 'false';} And the output was true when the condition is true. Again, when the condition is false (if(false)) it echos false. But i tried the same,...

Comparing cell contents against string in Excel


string,excel,if-statement,comparison
Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

How to write formula for cell in Excel?


excel
I'm working on excel where I've few columns. I would like to make SQL insert operation from the records in sheet. There are chances of cell to be empty and this is where I am unable to continue. I need to check: if(cell is empty) insert null else insert value...

VBA “Compile Error: Statement invalid outside Type Block”


excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

adding variables into another variable vba


excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

Using a cell's number to insert that many rows (with that row's data)


excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

Display only values containing specific word in array


php,arrays,json,string,if-statement
Right now I am working on a search function for my json decoded results. The code that I got looks like this: <?php foreach($soeg_decoded as $key => $val){ $value = $val["Value"]; $seotitle = $val["SEOTitle"]; $text = $val["Text"]; echo '<tr><td>' . $value . '</td><td>' . $seotitle . '</td><td>' . $text ....

The program after if statement is not exectuing


python,if-statement
I have a problem with escaping from if, my code looks more or less like this in python: if answear == "Yes": do something pylab.show() if anwear == "No": do something pylab.show() answear2= input("How are you?") print ("I'm here") The problem is that is statement works nicely but after showing...

12 Characters Including leading and following zeros


excel
I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

Interface Controls for DoEvent in Excel


excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

Chance of a conditional occurring in Swift: Xcode


xcode,swift,if-statement,conditional,percentage
I am quite new to Xcode and swift so sorry if this is a bad description. Is there a way that there is a chance of your conditional occurring so instead of it happening 100% of the time when the conditions are met, it only happens 50% of the time...

Using VLOOKUP formula or other function to compare two columns


mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

VBA - Unable to pass value from Private to Public Sub


excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

Stop Prevent Default in else clause


if-statement,mobile,menu
Hello I have a mobile nav where some items have a subnav so I have set a preventDefault to stop these doing there normal actions and open the sub menu but some don't have a submenu ad now these don't work as normal. Any help would be great here is...

excel search engine using vba and filters?


excel,vba
I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file


java,excel,apache-poi,writing
I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

Excel - select a cell based on adjacent cell value


excel
I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

R: recursive function to give groups of consecutive numbers


r,if-statement,recursion,vector,integer
Given a sorted vector x: x <- c(1,2,4,6,7,10,11,12,15) I am trying to write a small function that will yield a similar sized vector y giving the last consecutive integer in order to group consecutive numbers. In my case it is (defining groups 2, 4, 7, 12 and 15): > y...

Sort multiple columns of Excel in VBA given the top-left and lowest-right cell


excel,vba,excel-vba,sorting
I am trying to sort these three columns (Sort By Col-2) in excel using VBA. Top-left (Row number and Column number e.g. 1,1) and lowest-right cell (Row number and Column number e.g. 9,3) are known. Every cell contains the values of String type. Input: Col-1 Col-2 Col-3 P1 I1 XYZ...

>= not working, R [duplicate]


r,if-statement,double,logic
This question already has an answer here: Why are these numbers not equal? 3 answers I am running the code below. The function Ax returtns the value 1. But the ifelse statement does not recognize that Ax==1. I've tried making the output of the function double-precision and all numeric...

if condition in try process


android,if-statement,try-catch
i have some problem when trying to implement condition in "try" process, the result always of "globalnidn" always isi , therefore i've checked in logcat and the result is fine. :( protected void onPostExecute(String data) { try { JSONObject jObject = new JSONObject(data); globalnidn = null; globalnidn = jObject.getString("nidn"); String...

How do I check whether a file or file directory exist in bash?


bash,if-statement
I currently have this bash script (which is located in my home directory, i.e., /home/fusion809/ and I am running it as root as it's necessary for the icon copying lines): cd /home/fusion809/Pictures/Icon* declare -a A={Arch,Debian,Fedora,Mageia,Manjaro,OpenSUSE} declare -a B={Adwaita,Faenza,gnome,Humanity} for i in $A; do for j in $B; do if test...

Using a stored integer as a cell reference


excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

Identifying cell in Openpyxl


python,excel,openpyxl
I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

How to insert excel formula to cell in Report Builder 3.0


sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?


excel
I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

If cell value starts with a specific set of numbers, replace data


excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

Jquery, Javascript click && value


javascript,jquery,if-statement,click,int
how do I check inside jquery if a button is clicked and if the variable test has an integer of 1? I have done this so far. $(".bx-next").click(function() && (test == 1){ $("#step_1").text("Hello world!"); }); ...

Java dice roll with unexpected random number


java,if-statement
I've written a simple Java program to display the results of 20 dice rolls on the console. The results I'm getting are listed below: 3 1 [email protected] 1 5 4 1 [email protected] 1 [email protected] [email protected] 1 6 [email protected] 1 [email protected] [email protected] 1 2 3 3 When I ran it for...

Converting ADODB Loop into DAO


excel,vba,ms-access,ado,dao
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

using if statements in tableau on a contains function


if-statement,contains,tableau
Say I have city populations for each state / city in the united states. I want to mark each city with over 2 million population as a big city. then if a state contains a "big city", i want to mark that state as a "big state". I made a...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range


excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

Stopping condition on a recursive function - Haskell


string,function,haskell,if-statement,recursion
So, I have this function which aims to align the text on the left without cutting words(only white spaces). However my problem is that I cannot find a stopping condition of the function and it goes infinitely. f n "" = "" --weak condition f n s = if n...

How does this code print odd and even?


c,if-statement,macros,logic
#define MACRO(num, str) {\ printf("%d", num);\ printf(" is");\ printf(" %s number", str);\ printf("\n");\ } int main(void) { int num; printf("Enter a number: "); scanf("%d", &num); if (num & 1) { MACRO(num, "Odd"); } else { MACRO(num, "Even"); } return 0; } Please explain the above code (if/else condition and how...

jQuery - clearInterval seems not to work


jquery,if-statement,intervals,clearinterval
Long story short: If the interval count exceeds 5 the code should clear the interval, stopping it from executing the function every second. But this doesn't seem to work. HTML <div id='feedback'></div> JavaScript / jQuery window.intervalcount = 0; var interval = setInterval(function () { intervalcount += 1; $("#feedback").text(intervalcount); }, 1000);...

Excel VBA Loop Delete row does not start with something


excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

How to convert excel data to json at frontend side


excel,user-interface
I want to convert some excel data to JSON. Plan is to get my excel file from D drive, read data and make some UI for this. Can any one please help me out? Data is like this :- country year 1 2 3 4 Netherlands 1970 3603 4330 5080...

How to make nested IF statements neater


python,if-statement
This question could be asked in a language independent way but I'm asking in the context of python since different languages will have different nuances. I recognize that this is similar to this question but I think mine is different in that I'm asking about a case where putting the...

How do I do to count rows in a sheets with filters? With a suppress lines


excel,vba,filter
I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

Excel - Pulling data from one cell within a list


excel,powerpoint,spreadsheet
I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

timestamp SQL to Excel


php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

If/else problems in python


python,if-statement
Ok, I'm gonna explain this as best I can. I am trying to make an if/else statement in python that basically informs the user if they put in the right age based on raw input, but I keep getting an error in terms of my if statement. Can I get...