# Use Vlookup For Negative Values Or A Range Of Values?

Oct 27, 2008
i want to put a subject for a negative values, how should i do it?

For example:

lookup_value: -1000

table_array:

<0 - Poor

0-10000 - Good

> 10000 - excellent

is it possible to do this? ALternatively see the (new) attachment for clearer question.

View 8 Replies
ADVERTISEMENT
Jan 19, 2009

I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

View 3 Replies
View Related
Jan 25, 2014

I am currently working on a project and encountering this problem with VLOOKUP.

I need to show the result into negative since it is negative on the table i made.

In my this scenario, it gives me this error "#N/A" even if the value is in the table.

Here are the details:

3 columns

RANGE Minutes

0.02

0.03

[Code].....

my actual table is upto minutes of +960 and -960 and still planning to go up until +1500/-1500

I have named the table as "TABLE" and the value is on "F3" I am using the command =VLOOKUP(F3,Table,3,TRUE) but if the cell is negative it return positive value how do i change the formula so it return the value to negative if it shoud be negative?

I also attached the file as your reference.

I need this to get the value in minutes if it is within the value in the range.

View 9 Replies
View Related
Mar 26, 2014

I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.

Formula:

[Code] ....

View 2 Replies
View Related
Aug 14, 2008

is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

View 9 Replies
View Related
Apr 7, 2008

I have a table (column C5 to H20)

The table has values in each cell. Some cells have zero values and some have positive values.

Basically, I need to convert all positive (non zero) values to Negative values using VBA (i.e. convert 251 to -251).

View 9 Replies
View Related
Dec 6, 2013

i have a set of data in which i'm trying to lookup a value from a range of file #'s (ex 100-125) that i have concatenated with a file ID (ex 123456789). as of right now i have to vlookup each individual concatenated number, return the result value in its own column (one for 100, one for 101 and so on..) and then sum all 25 columns to get the final number) is there a way to vlookup the range (ID+100:125) and sum the results?

here's the formula i use for each column starting with 100, A2 representing the file ID, C:O being the array

=IF(ISERROR(VLOOKUP(A2&100,$C$2:$O$10000,13,FALSE)),"0.00",VLOOKUP(A2&100,$C$2:$O$10000,13,FALSE))

View 14 Replies
View Related
Oct 9, 2012

I would like to sum the values of a range using something similar to vlookup. The problem with using vlookup is that vlookup returns only the first instance. I have multiple values therefore vlookup would not work. I have 2 variables which is causing my frustration. I have added an example to explain. On the column where it says "correct answer" is where the formula should go. I displayed what the formula should produce so that it explains what I want as the output. The "static column" which contains variables 1 and 2 doesn't change.

The two variables it needs to search for is shown in the variable 1 and variable 2 columns. And the lookup range is on the left.

So basically it needs to match the two variables in the range and then grab the sum of ALL quantities where the two variables match . quantity amount is found on the leftmost column.

Is this possible. sumif works but only with 1 variable.

LOOKUP RANGE

formula column

static column

static column

formula column

static column

static column

quantity

variable 1

variable 2

correct answer

variable 1

variable 2

[Code] ......

View 3 Replies
View Related
Mar 5, 2009

Column A includes a name

Column B includes an age

Column C states a division (this is the column that contains the Vlookup function)

Column G is where I have to put a range of ages (for instance, x=<0,>3)

Column H is where the division name is.

The idea behind this exercise is to use the Vlookup function in column C to reference column G, and return a value in column H. Here a copy of my formula in column C: =VLOOKUP(B3,$G$3:$H$12,2,TRUE). For instance, if Mike (A) is 16 (B) then he would be in the Midget division (C). C is calculated by comparing column B to G, then returning a value in column H. For some odd reason, I can't get column G to reference an age range, such as 1-5 years old.

View 2 Replies
View Related
Apr 30, 2014

I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.

For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.

Sample:

John Japanese

John Chinese - Simplified

John Korean

Martin Arabic

Martin Chinese - Simplified

Martin Russian

Ramon Arabic

Ramon Russian

Sam Arabic

Sam Chinese- Traditional

View 1 Replies
View Related
Jul 23, 2014

two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)

I need to create two formulas:

1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.

2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.

View 8 Replies
View Related
Dec 11, 2003

Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows.

Ex

B1:B70 (remember the number can change it can also be B1:B80)

B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point.

Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.

View 9 Replies
View Related
Dec 8, 2009

I need an if statement that says if cell a1 is Blank or negative, if true a2, if false a3

View 2 Replies
View Related
Sep 3, 2009

way using vba to match a -negative value to a postive value in a same column and delete the entire row? (debits and credits)

View 9 Replies
View Related
Apr 18, 2008

I am trying to figure out how to ignore a #VALUE! error in my OR formula. I am using this formula for conditional formatting and I don't want to correct the error because I am using the errored out cells as a fourth conditional format since you can only use three in excel. This is my formlua:

=OR(N4>0,O4>0,P4>0,Q4>0,R4>0,L4>0,Q4>0,K4>0)

How can I modify this formula so that the whole formula doesn't error out if one of the cells has #VALUE! in it?

View 5 Replies
View Related
Mar 7, 2014

Using AVERAGEIF on a row containing both positive and negative values one can easily calculate the Average of only the positive or negative values. I need to filter the 20% outlier on each case, how can I do that with TRIMMEAN ?

This is an example of the current formula using Averageif for the positive values:

=IF(C3=0;0,0001;AVERAGEIF(Auxiliary!B29:BL29;">="&O3))

For now O3 can assumed to be 0.

View 8 Replies
View Related
Dec 15, 2009

I've hit a wall for the first time in my excel using career, I’ve searched the net high and low, asked the guys in my IT department and am so far stumped for a solution.

The problem is thus:

I have 3 columns, A, B and C.

Column A and B contain two numbers. Column A is "telephone calls offered" Column B is "Telephone calls Answered".

Column C is a calculation of the percentage of calls NOT answered. The formula I am using for this is =(A1-B1)/A1*1

This gives me a % as an answer.

Rows 1 to 66 are different time periods through out the day.

The problem is that for some fields, we don’t receive any calls, at all. Or we receive a call in one time period, and it is answered in another.

so Lets say cell A1 is 0, and Cell B2 is 3. Or A2 is 0 and B2 is 0.

How is it possible to get excel to display 0% if, after the calculation the value is < or = to Zero?

I have tried the below "IF" function but it returns a #Value error

=IF((A1-B1)/A*1<=0,"0%",(D67-E67)/D67*1)

I now leave it to the capable hands of the anonymous internet gods of excel

View 2 Replies
View Related
Feb 2, 2009

All i am trying to do is create a function that will provide me with the minimum invoice value between cells H17:H150 which excludes negative values i.e. refunds ...

View 6 Replies
View Related
Aug 20, 2003

Negative or zero values cannot be plotted on log charts!!

Some of my log charts will have negative or zero values at certain input variable combinations, however, I'm obviously not interested in those charts when this is the case.

View 6 Replies
View Related
Feb 11, 2010

I have a formula where I am taking a fixed amount away from a cell (i.e. dragging it to the right) until it reaches zero. I do not want the answer to be negative and when it gets to that stage I want it give the remainder. For example:

100 - 35 = 65 30 5

View 9 Replies
View Related
Oct 29, 2012

I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).

View 4 Replies
View Related
Jan 24, 2014

I've got 12 months worth of business accounts which have been downloaded onto a single spreadsheet.

These show negative and positive values. As I only want to work on the negative figs at the moment, how do I highlight the colour of the positive value cells or font to prevent any mistakes ?

View 1 Replies
View Related
Feb 23, 2010

I have a very large worksheet (row count maxed in 2007, and then some), for which I need to do the following: search column A for a string that will occur many times, and then check the 10 cells that follow in its row for negative values, dropping some sort of indicator in the 11th (shading it red or something would be fine). An additional bonus would be if the 10 cells that possibly contain a negative could be summed (the sum could serve as the indicator?). If no negative is found, nothing need be done, and the macro should chug along searching A for the next reference to this string.

My hope was to do a sort of "With Range("A:A"), .Find("MyString")", save position as StartPos, do the 10-cell row checking in a nested IF or For (though the For would take a long while, checking each cell individually), then doing a .FindNext after StartPos until = StartPos (does .FindNext loop back to the top?). The formatting of the indicator cell in the 12th cell in each relevant row doesn't really matter, it's more just for jumping to critical rows.

View 3 Replies
View Related
Oct 29, 2012

I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).

View 1 Replies
View Related
Dec 11, 2013

I am attempting to add conditional formatting (yellow fill) to cells that are greater than 15% or less than -15%. I've tried the following formula but, it highlights all cells.

=or(b2:b5>15%,b2:b5

View 1 Replies
View Related
Apr 13, 2014

I have a sheet which contains credits and debits for several accounts:

Customer

Amount

ABC

2000

XYZ

3000

XYZ

1000

ABC

1000

ABC

-5000

XYZ

1000

What I need to do is match negative and positive values within each account and highlight them. I have been able to put together a VBA code from google and other forums to match within all rows.

Code:

Sub matchdata()

On Error Resume Next

For Each cl In Columns(10).SpecialCells(2, 1)

Err.Clear

[Code] ........

1. I need to adapt the code so as to do matching within accounts. Example within ABC and XYZ separately.There will be 100s of accounts.

2. If possible, I need to offset a credit (negative value) with multiple debits. Example: ABC -5000 would offset ABC 2000 and ABC 1000. I am not worried about the remaining balance.

View 1 Replies
View Related
Feb 6, 2007

I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value.

Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures.

The invoice number format is 8 characters beginning with a letter and credit notes begin with a number.

Is there macro code that will change credit note values to a negative figure?

View 9 Replies
View Related
Dec 10, 2008

In Sheet1 from row 12 on down in columns A to E I have numerical values.

Column F has letters, either N or S

Column G has numerical values

Column H has letters, either E or W

and columns I, J and K have numerical values again

On a new sheet (Sheet2), I want everything copied but the numerical values

in columns G need to be changed to negative if the cell to the right, column F,

have an S and the values in column G need to be changed to negative if the cell

to the right, column H, has a W.

Columns F and H are therefor not copied to Sheet2

(Sheet1 has quadrant readings and Sheet2 will end up with azimuth readings)

The result is that Sheet2 will have all numerical values from column A to column I

with columns E and F either positive or negative values.

Currently I use the following

Sub CopyTNS()

Sheets("Sheet1").Range("A12:D" & Range("D65536").End(xlUp).Row).Copy _

Destination:=Sheets("Sheet2").Range("A12")

Sheets("Sheet1").Range("I12:K" & Range("K65536").End(xlUp).Row).Copy _

Destination:=Sheets("Sheet2").Range("G12")

View 9 Replies
View Related
Jan 30, 2009

Below are cell values a1:c6

a 2 1

b 3 3

a 4 5

b 5 3

a 3 7

a 4 2

I want to grab the value from a cell whose position is relative to cell C5 (value= 7).

i.e.

the value from column B

of the first row ABOVE cell c5

with 'b' in column A.

I presume an index statement might do it, but I am unsure how to search for a row above a reference cell.

View 9 Replies
View Related
Feb 5, 2009

I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total.

I would like to also have it show the summation of all negative values per month. So it would be:Jan Total

Jan Negative Total

Feb Total

Feb Negative Total

etc...

Grand Total

Grand Negative Total

Is it possible to add such functionality?

Image for reference:

View 9 Replies
View Related