Search results

helicopter, that throws up an error. i was thinking, would and Is Error combined with my IF statement make the div0! go away?

edit, the formula doesnt work properly if all list is 0. still gives div# error if you look in the attached tracker2 file, in the 2nd coloum, the average price returns a div# error. this is what i want to aviod, i would rather have a 0 in place of that div#.

edit, the formula doesnt work properly if all list is 0. still gives div# error

thanks helicopter, that sum formula has done the trick. btw, im a bit confused as how the formula works?

hi, i have attached the file, using the formula you have supplied darth v, i get 666.667. i should be getting 20,000 as this is the only value in the list. can you have a look and see whats going with the calculation please?

hi, i only want to average the list of values which are over 1, if they are 0, then i dont want it to be averaged. e.g. my list of values: 40 334 0 35 0 233 i want my average statement to average this list ignoring the 0's, which is what this statement does: =AVERAGE(IF(AP155:AP184<>0...

i want the formula to: if the coloumn of numbers are all blank, then the output is zero, otherwise calculate the average price of the values in the column.

i have kind of got my statement to work, =ISERROR(AVERAGE(IF(AN155:AN184<>0,"mcdonalds",AN155:AN184<>0))) it should say mcdonalds, but comes back as True instead. any help on this?

hi, my formula looks at a column of values and gets the average. problem is when there are no values in the column, it gives an error of #VALUE. the formula is: =AVERAGE(IF(ISERROR(AP155:AP184<>0),"NA",AP155:AP184)) can any1 help me please?

SJ, i think i have a better way of going about this formula. my statement now is: =IF(AND(C3="IBM",OR(F3=39234)),J3,0) Regarding Cell F3, this is a 'date' formatted cell. Excel stores dates as numbers, the number for 1st June 2007 is 39234 in excel. what i would like to do is: IF Ibm is...

looks like excel interprets dates as numbers, going back from the year 1900 as number 1. Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after...

hmm, that formula doesnt work, it gives an error box when i enter it.

the date field (F3) is custom formatted, mmm-yy edit, its definatley somthing up with the date field (F3), as when i select another field instead of F3 for the OR criteria, e.g. =IF(AND(C3="IBM",OR(E3="UK")),J3,111) it picks up "UK" with no problem, so there is something wrong the date field...

hmm, this formula seems to return back 0 when the conditions are met, its not reading cell J3. i dont get it.

hi, i want to enhance my IF statement to include 2 more conditions. my current IF statement has 1 condition of only checking if IBM is present. =IF($C3="IBM", $J3, 0) what i want to do: (IF statement with '3' conditions). if cell C3 = IBM, and if cell F3 = 'Jul-07' or 'Aug-07', then display...

i.e. if i do the average of a list of figures, and if 1 of these figures has a few words in it, it changes the average calculation value. it seems to ignore the cell with a figure and writing in it, what can i change in my average statement that makes it ignore the few words that it may see in...

hi, i have an average statement which calculates the average prices =AVERAGE(IF(AG7:AG16<>0, AG7:AG16,"")) from a list of figures, this list of figures looks at table and selects the prices using an IF statement which looks at a drop down list and selects the corresponding value from cell 'J7'...

help :eek: in the form "order" , its not displaying the total cost of all the books for each order id, also, in the report "Orders 1", i cant get the sum total figure to work. any help ?

lol, never thought about that ! thanks, the report has to be a invoice to the customer, so i gotta add customer address,etc fields into it. many many thanks melt down, i would have been here all night trying in vien !

thanks meltdown. it was that darn Sum button then ! my next problem is, when i create a report to make a invoice, it displays all the Order IDs, i just want it to show 1 order id and hence only the books ordered on that order id. if you look at the report "orders 1", then you will see what i...