# Negative Or Zero Values Cannot Be Plotted On Log Charts?

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
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
Oct 14, 2008

I am trying to get some help by adjusting my datarange dynamically based on what days I select. I would hope the chart could adjust to display for example 10/3/2008 to 10/10/2008 values and only those values.

I have been trying to figure out how the datarange varies in this post Dynamic Chart dataranges, I do not understand how to apply them to the charts source data. Ultimately Krish's chart would be ideal to understand and I belive is the keystone to where I am trying to go.

I have attached a sample of my workbook and the source data and chart I am trying to manipulate.

View 4 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
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
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 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
Feb 6, 2007

I have created a dynamic chart using defined names, and the cells in the range are updated on a monthly basis. However these cells are copied from another sheet using a formula i.e =sheetname!$cell_no. and until that data is entered they have 0 values - my question is, is there a way to suppress the 0 values which are being 'plotted' on the dynamic chart until they have appropriate values?

View 4 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 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
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
May 10, 2014

I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.

In the excel sheet the PV needs to be the x-axis.

View 1 Replies
View Related
Apr 1, 2009

Is there a way to limit the range of values on the Y-Axis on the lower end? Like I don't wish the Y-axis to begin from 0 but say 500. Is that possible?

I have a set of data values ranging from 650 - 850, but the Excel graph however looks too fine because the Y-axis begins from 0.

View 6 Replies
View Related
Sep 28, 2011

I would like to know how can I plot my x axis of a chart with the maximum and minimum value that I have in this variable. Note that my variable is no going from the lower value to the highes one, it is random. So what I did is to use the autoscale of the chart, but it is not working as I would like. Any solution?

The code that I use is:

Code:

' TO GET THE INITIAL and FINAL VALUE and the STEP

Worksheets(wsheet(27)).Activate

ActiveSheet.ChartObjects(chartName(11)).Activate

ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True

ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True

ActiveChart.Axes(xlCategory).MajorUnitIsAuto = True

xScaleMin = ActiveChart.Axes(xlCategory).MinimumScale

xScaleMax = ActiveChart.Axes(xlCategory).MaximumScale

xScaleStep = ActiveChart.Axes(xlCategory).MajorUnit

'After I use them (xScaleMin,xScaleMax,xScaleStep) for several charts, in this way:

ActiveSheet.ChartObjects(chartName(11)).Activate

ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text =VarName

ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Select

ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11

ActiveChart.Axes(xlCategory).MinimumScale = xScaleMin

ActiveChart.Axes(xlCategory).MaximumScale = xScaleMax

ActiveChart.Axes(xlCategory).MajorUnit =xScaleStep

' but if my data was between 50 and 250 it is plotting between 0 and 300

View 2 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