Formula Not Giving Correct Output?

Mar 24, 2014

I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.

B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0

I have attached the sheet also.

Sheet 1.xlsx - Speedy Share - upload your files here

View 2 Replies


ADVERTISEMENT

Formula Not Giving Correct Output?

Mar 24, 2014

I have a fomula field in row 67 and 68 .Row 67 calculates the average and row 68 calculates the improvement i.e. average(row 68)- last row inserted(row 67). The values for improvements are not showing correct value. C68 seems to give correct value but B68 and D68 is giving wrong value.

B68=4.8-4.5 =0.3 but it shows 0.4
C68=14.9-15.1=-0.2
D68=11.3-10.2=1.1 but it shows 1.0

I have attached the sheet also.

Sheet 1.xlsx

View 11 Replies View Related

Correct Formula Not Giving Results?

Feb 25, 2014

Cell A3 on my SHEET1 has the following formula =DATE(YEAR(C1),MONTH(C1) + 1,DAY(C1). It goes on like this for the next 12 months (+2, +3, +4....).

Column B on my SHEET2 has One of the five product codes (A, B, C, D, E) and Column D on my SHEET2 has manually entered dates

I created the following condition for each product code under each month in SHEET1

=IF(MONTH(Sheet2!$D$1:$D$39)=MONTH(Sheet1!$A$3),COUNTIF(SHEET2!$B$2:$B$39,"=A"),"")

The formula only works for two product codes (A and B), and then yields blank cells.

View 5 Replies View Related

Addition Calculation Not Giving Correct Answer

Feb 20, 2009

I have a workbook with calculations for a sale less the assorted fees and at the end giving the final amount from a sale.

I have noticed that some of the rows are not giving the correct amount in them.
In other words the addition of some columns in that row are not adding up correctly. It is only off by 1 cent (either over or under), but I can't figure out why.

I have the feeling that I am going to want to kick myself when someone explains this to me (I just know that I know the answer but for the life of me I can't right now).

View 2 Replies View Related

Sumif Function Not Giving Correct Total

May 24, 2012

I'm trying to add the total product sold by a group of sales person for example : Chad + Javier. My actual worksheet includes 600 rows of salespeople and 20 rows of product, i'm using this simple example to get the message across.

I was using =sum(sumif(A1:A9,"Chad',D1:D9),sumif(A1:A9,"Javier",D1:D9)), the formula should have given 91, but instead is calculating 113...

Salers PersonProduct AProduct BProduct C
Chad567
Chad567
Chad567
Chad567

[Code] .....

View 1 Replies View Related

Substitute Function Giving TEXT Output?

Oct 2, 2012

I've in cell A1 an entry like this: 123*456*7890

=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE

BUT, =A3+1 gives me: 1234567891

Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?

View 2 Replies View Related

Get Correct Output Of Superscript?

Jul 17, 2014

I want to superscript the text with red color. But now only a active cell is change to superscript with red color after running the macro.

View 2 Replies View Related

Getting Error 1004 And No Correct Output?

Jul 17, 2014

There are 2 macros in the workbook.

APlusB: Taking the marks of first subjects for all subjects. No correct output no any compile error.

Total: Getting compile error 1004.

View 4 Replies View Related

Countif Formula: Added The Correct Amount In The Correct Cells

Feb 15, 2010

I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.

View 4 Replies View Related

Mulitiplying In Formula Giving Incorrect Answer

Dec 20, 2009

I have a formula that is correct all the way up to adding the last *K9. here is an

example:

C9 a number 4 then add 1 to it which makes it 2 now * it by D9 which is the number 384 and the answer is 768 now * that by K9 which is 6 and you get 4608 I'm getting 2310 using this formula... =IF( C9=0, 0, C9+1*D9)*K9.

View 3 Replies View Related

Simple If Greater Than Formula Giving Error

Jan 19, 2012

I'm getting a mismatch error on my If activecell.value > 5 Then portion of this code. I'm trying to say if the value in column AG is greater than 5 then create an email address from the first and last name. I've even made sure to copy and paste the formula results as values.

Code:
Sub CheckPFPDates()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim emails As String
Dim salution As String
Dim currenttime As Integer

[Code] ........

View 2 Replies View Related

NetWorkDays Formula :: Giving 0 Instead Of 1 (leads To Negative Values)

Oct 29, 2008

I am having an issue with this formula. The formula works for 85% of my data. I worked through the formula and found that the error occurs within the networksday part of the formula. For some reason the it gives me a “0” when it should be giving me a “1”. This causes the solution to be a negative and create the error. I placed an example of when both the formula works and doesn’t work. I tried many things. Love to get some other ideas why this is happening. It’s a altered Daddylongleg formula....

View 6 Replies View Related

Formula Cell Not Tampered And Correct Formula

Feb 27, 2012

I have two questions:

1. Is there a way cells with formulas cannot be tampered even if they are not locked? So when I enter a data on a cell with formula, the formula won't be erased? The sample below shows that C1 and C2 has formulas but let's say I enter a value on C1, the formula should still stay? Is this possible?

A B C
1 5 10 =SUM(A1:B10)
2 2 4 =SUM(A2:B2)

2. Let's say I have a total of 125 (A1) and I need to get the answer (B1) where it has 5 with No. 3 on top (subscript) of 5? How to create a formula for this (I hope I make sense)?

View 1 Replies View Related

Check Cells Are Correct Format And Contain Correct Data

Jan 26, 2014

We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.

I envisage something like an output report:

#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################

View 4 Replies View Related

Sumproduct- Counts The Correct Amount But Not With The Correct Dates

Jul 3, 2008

I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.

I need to see how many items processed for each set per day.

Example:
[url]

The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))

View 14 Replies View Related

How Can I Correct This Time Formula

Nov 4, 2005

How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####

=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)

What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).

My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell

View 23 Replies View Related

Correct Formula Related To Dates?

Aug 5, 2014

correct formula related to dates;

Assume cell A1 contains a date in DD-MMM-YYYY format. I would like to have a formula that looks in A1 and compares to the current date (TODAYS Date) and if the value in A1 is todays date or greater return "YES" and if not return "No".

View 2 Replies View Related

Formula To Correct The Numbers In The Next Column

Nov 3, 2008

I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??

View 2 Replies View Related

Formula Correct But Not Showing Answer

Mar 11, 2013

I am using this formula for long now

=SUMPRODUCT(('[Diesel Statement.xlsx]Nov 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Nov 12'!$G$2:$G$251)

every month i just add a new sheet and change the month name in the formula to that eg

I changed it to

=SUMPRODUCT(('[Diesel Statement.xlsx]Dec 12'!$C$2:$C$251=$H$1)*'[Diesel Statement.xlsx]Dec 12'!$G$2:$G$251)

but now it is not calculating the answer is coming as #value!.

Then I tried to add up by

=VLOOKUP($H$1,'[Diesel Statement.xlsx]Dec 12'!$C$2:$G$215,5,TRUE)

but it does not add all the entries only one entry is shown.

I want the total of the column G. looking up for values in C = H1 value

View 7 Replies View Related

Formula Calculates Correct Value But Does Not Display

Mar 18, 2008

Excel 2003 SP2
--------------
I have been programmatically setting the formulae in a worksheet. I have various formulae:

Example 1 - =COUNTIF(Data!M$6:M$9871,3)/$E$4
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 56.80%
The cell displays 56.80%, all is good!

Example 2 - =SUM(IF(Data!$J$6:$J$9871=$D8,IF(Data!$M$6:$M$9871=3,1,0)))/$E$8
The cell is formatted Percentage, 2 decimal places.
Using the formula evaluator the value returned is 53.37%
The cell displays 0.00% !?!

View 9 Replies View Related

Output Formula Result

Feb 14, 2014

How do you write a code that will calculate a formula and output the result in a cell?

For example:

A1 = 1 (Named "Cell1")
A2 = 2 (Named "Cell2")
A3 = Results of macro for Cell1+Cell2 = 3

Is there a way to write it if I also want B3 = B1 + B2 and C3 = C1 + C2, etc.

View 6 Replies View Related

Formula Array Output

Jan 28, 2008

I am trying to get a list of states, based on their expiration date.

I have all 51 states listed, some states have 2, 3 or more licenses, so I created a column that I hid of the state abbreviation, for the use of formulas.

If the license expires in 30 days or less I want to display the state abbreviation. I couldnt figure out how to do an array output, so this is what I am trying to come up with.

=IF(COUNTIF('Kevin Doyle'!F5:F250, "

View 9 Replies View Related

Get Correct Value Using Vlookup Formula In Duplicate Look Up Values?

Mar 4, 2014

How to get correct value using vlookup formula in duplicate look up values.

Here i mentioned a eg;

VlookUp_DuplicateValue.JPG

View 8 Replies View Related

SumProduct Formula Not Gathering Correct Data

Dec 17, 2009

I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.

Here is the formula I am using:

=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)

Please see attached line S18 for the error I am getting.

View 9 Replies View Related

LOOKUP Formula Not Pulling Correct Data

Mar 27, 2009

Attached you will find the spreadshhet in question. If you click on the click here button input the following info:

8.0
3508949
80
0
0
25

the sheet will auto populate all numbers. this part is working properly. MY issue is if you look at the Bad Debt number, the correct response should in cell f21 should be $636.46 because in order to hit the next level of pay, you must at least hit $3,424,082.00. I can not figure out how to get the correct formula that will put the right pay out. Basically in order to hit a certain level of payout you must hit that next number, even if you are a $1 higher you can not get the pay out. So for example if your bad debt is $3,424,083 you would be paid out at 100%, not 120%.

View 3 Replies View Related

Sum Macro With IF Formula - Enter Correct Value Into Column

Dec 8, 2011

I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN β€œQ” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.

I want the Macro to do the following THREE THINGS:

1. IF the Value in COLUMN L is β€œ0” THEN enter β€œn/a” into COLUMN β€œQ”
2. IF the Value in COLUMN L is β€œ2” THEN enter β€œn/a” into COLUMN β€œQ”
3. IF the Value in COLUMN L is β€œ1” THEN use a formula that looks like this:

= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a β€œ2” in COLUMN L and then MULTIPLY that Result against those rows with a β€œ1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN β€œQ”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.

Here is an example of how the formula will work:

I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are β€œTimes” and β€œPM” and contain a β€œ2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a β€œ1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN β€œQ” IS 10.395.

Here the DATA SET:

Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ

This is what is happening in Column Q Explanation of Formula

Row 2
Times
Cont
2
28 28 n/a

n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".

Row 3
Times
Cont
1
404 1194 413.4740369

O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L

[Code] ...........

View 9 Replies View Related

Formula To Check Correct Reporting Period

May 18, 2012

Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.

I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.

Reporting period is shown in two columns Year and Period number (1 to 12).

View 4 Replies View Related

Make Formula Output Another Word Again In Next Row Down

Aug 15, 2014

In column A i am using the following formula to output "Our Apple" when "Apple" is in column D and "Our Banana" when "Banana" is in column E:

[Code] .....

I would like the formula to also output "Big Apple" immediately underneath "Our Apple" when its present, and output "Big Banana" immediately below "Our Banana" when it is present.

The yellow column is how column A should look after the formula edit.

Attached File : Book2.xlsxβ€Ž

View 5 Replies View Related

How To Get Output In Hindi Numbers By Formula Used

Jul 24, 2014

I'm using "Mangal" Hindi font for entering the data but I'm not getting Hindi numbers in the output by formula. The output is in English numbers which I want in Hindi numbers. The marks are in Hindi numbers but the output is in English numbers.

The remark in column "bz" should be in English.

How to get the output in Hindi numbers.

View 4 Replies View Related

Formula To Manage Output Orientation

Feb 9, 2010

Assuming I’ve the following sample data and wishing if the output data could be formatted using some excel formulas, refer to below sample.

Sample data (Sheet1):
StudentA | Lesson1
StudentA | Lesson21
StudentA | Lesson10
StudentA | Lesson5
StudentB | Lesson5
StudentB | Lesson6
StudentC | Lesson9
StudentC | Lesson8
StudentC | Lesson9
StudentC | Lesson15
StudentC | Lesson11
StudentC | Lesson30
StudentC | Lesson13
StudentD | Lesson1
StudentD | Lesson2
StudentD | Lesson3
StudentD | Lesson4
StudentD | Lesson5
StudentD | Lesson6
StudentD | Lesson7
StudentD | Lesson8

Sample output (Sheet2):
StudentA | StudentB | StudentC | StudentD
Lesson1 | Lesson5 | Lesson9 | Lesson1
Lesson21 | Lesson6 | Lesson8 | Lesson2
Lesson10..................Lesson9 | Lesson3
Lesson5....................Lesson15 | Lesson4
.................................Lesson11 | Lesson5
.................................Lesson30 | Lesson6
.................................Lesson13 | Lesson7
..................................................Lesson8

The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.

Please refer to the attached file for some sample data.

View 9 Replies View Related







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