• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

EXCEL FORMULA

X

xcruc1at3r

Member
i have a database on a large scale

B COLUMN L COLUMN P COLUMN R COLUMN S column Tcolumn

PRODUCT CODE Date HR METER CATEGORY HR meter/differnce No. of Days

APJH-1234 15/2/11 2345 COM


there are to categories Com and Usm

there are 800 product codes...and i have a inventory on the sheet1 and sheet2 is the database Okay...

for e.g. if apjh-1234 dated 15th jan, 11 is on the 1 row with hr meter 200

and the same product code comes randomly in the list could be anywhere in the list could be on the 2nd row or the 10000row!...when is appears in the row so the DIFFERENCE of The Newly added apjh-1234 27th march,11 Hr meter of 560

so the difference difference of the of current(560) - (200) previous =360 should appear in front of the Previous Entry..and the No. of Days that after how many days the news activity took place!...

is that possible!>>?

i am not sure that is there any way out!!
 
you gave me 6 column titles and 4 data columns as example. it's hard to grasp when things aren't written coherently. or may be it's friday and i'm a bit beat. sorry.
 
well the Fred-

The 5th and the 6th are the columns were need to place the formula!!

based on HR meter difference and the 6th column is the No. of Days
 
I thought so... that being said, however, what do you mean by


if apjh-1234 dated 15th jan, 11 is on the 1 row with hr meter 200??


it doesn't correspond to the sample.
 
well there are 800 different product codes... in the inventory sheet1 and in database sheet we place activity related to the product


for e.g the product entry was place in row no. 20 ( could be in any row)apjh-1234 was sold on the date 15th January 11 HR meter 200 is the no. of how may were sold...

and the same product apjh-1234 (Row no. 300 )was sold on 24 march 11 HR meter 550.1 times...

so i need to difference b/w the current and the previous reflecting in front of the previous entry... and the difference of no. of days as well...
 
and so On.. if apjh-1234 was again sold on 5 September 11 and the entry is place in (row no. 2000) with the HR meter 1200 (could be any)..the difference of current subtract previous should reflect infront of the previous one like 1200-550.1 = 649.9 in front of the previous one!...the process goes on !..with all the 800 products in the inventory sheet1
 
Can you post a file with a few sets of data and some notes to make it clearer?
 
here is the link : https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdDhmcXptbUNET3NIcW9nMEsyMWFkVnc&hl=en_GB


well the report should provide the results that viewer can check the Hr meter against selected multiple gensets...

its a sample report!...if you want to modify it sure go ahead! what ever makes your work easy...:)
 
Not sure what to do about report, but it looks like this will work on inventory sheet.

Hrs meter:

=IF(ISTEXT(D2),"",INDEX(D:D,MAX(IF(($B$2:$B$100=B2)*(ISNUMBER($D$2:$D$100)),ROW($B$2:$B$100))))-D2)


# of days:

=IF(ISTEXT(D2),"",INDEX(C:C,MAX(IF(($B$2:$B$100=B2)*(ISNUMBER($D$2:$D$100)),ROW($B$2:$B$100))))-C2)


Both of these are array formulas, confirm using Ctrl+Shift+Enter
 
Luke my friend ....U have seen the workbook right!..well in the "Database" sheet ...the Result should reflect infront of the concerned cell!... for e.g. abc is the Genset code

S.no..Genset....Date......HR Meter.....Hr Meter Difference

1......abc......1/1/11......200.........300-200 = 100

2......cde......2/1/11......100.5.......250-100.5=149.5

3......edfg.....3/1/11......150.........

4......abc......4/1/11......300.........450-200 =250

5......cde......4/1/11......250.........

6......abc......6/1/11......450.........500-450=50

7......abc......8/2/11......500.........and so on !...


same goes for the No. Of days :)...am not good that describing :(...hope it clear the picture ...
 
Oops, I got the sheets mixed up. My apologies. It looks like each record is just looking at the next date occurrence, correct?

Hrs difference:

=IF(OR(ISTEXT(D2),COUNTIF(B3:$B$77,B2)=0),"",INDEX($D3:$D$77,MATCH(B2,IF(ISNUMBER(D3:$D$77),B3:$B$77),0))-D2)

Formula will ignore entries with text entered for the Hrs.


# of days would then be:

=IF(OR(ISTEXT($D2),COUNTIF($B3:$B$77,$B2)=0),"",INDEX(C3:C$77,MATCH($B2,IF(ISNUMBER($D3:$D$77),$B3:$B$77),0))-C2)
 
lOLS !! its ok buddy pls dont apologies....pls after all u are helping me out:)...am sorry that i got u all confused bro....

well yes !... its a Meter reading based on the running of the genset... that it has been running and start for xyz hrs...during the travailing for A destination to B... and when again leaving for C destination to D the meter read was abc...so i can check what was the average running!!... between the destinations...so we can cover the miss handling and utilization...:)
 
In the OR function, we can add another condition to check for that, like this:

=IF(OR(B2="",ISTEXT(D2),COUNTIF(B3:$B$77,B2)=0),"",INDEX($D3:$D$77,MATCH(B2,IF(ISNUMBER(D3:$D$77),B3:$B$77),0))-D2)


=IF(OR(B2="",ISTEXT($D2),COUNTIF($B3:$B$77,$B2)=0),"",INDEX(C3:C$77,MATCH($B2,IF(ISNUMBER($D3:$D$77),$B3:$B$77),0))-C2)
 
You can change it in the 2nd callout (not the first, as that's checking for if there's any records left) as long as you also change the d3:D77 callout, AND you're using XL 2007 or greater. This will slow down the speed of the calculation, however, as that's a lot more cells XL will have to look at.
 
rite !!...gr8 advice...bro....so i'll extend the range according to the data....

well need a favour... luke if i want to get good command over arry formula's so from were should i start...!!..and learn
 
Scratch what I said earlier, you can't callout the entire column. Otherwise, it will look in the wrong place. The N/A error is coming from the MATCH function not finding any records. =(


Chandoo did an article on SUMPRODUCT...it natively handles arrays, and shows how they multiply and such.

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
 
Well so if there is no Meter reading so we need to place 0 rite !!...so match formula could cover it !!.. and wont generate #N/A error ! rite
 
well i really want to have a Treasure of knowledge like U guys... am really impressed by the Knowledge Bank u ppl have... its superlative... i really want to learn and help ppl out the way you and hui and others do
 
Luke my Friend!...In same Procedure can we calculate the Average No. of days & HR meter Utilization? genset wise same as the reading is...
 
For the average, can you use AVERAGEIF function? (need 2007 or greater)

If not, you can do an array type formula:

=AVERAGE(IF(CriteriaRange=Critera,ValueRange))


I don't understand what "HR Meter Utilization" would be a measurement of...can you elaborate?
 
You must log in or register to reply here.
Back
Top