Most Suitable Range Area On Average Term Accounting?

Erdinç E. Karaçam · Jul 13, 2008

Hi all,

We was discuss about how can we find the most suitable range area on average term accounting at a Turkish Excel board which is called Excel.Web.Tr linked as:

http://www.excel.web.tr/showthread.php?t=50787

But, there i wasn't enaugh to resolve exes's requirement in that thread...

The problem is a little complicated and hard to tell here, i think that i need a little Turkish help, if it possible?

Thanks a lot...

PaddyD · Jul 13, 2008

Hi - thanks for the heads up about the cross post, but you'd be best to try & explain your problem here too - can't be sure that everyone's going to be comfortable navigating to an unknown site...(not to mention that it seems you need to register to view?!?)

Erdinç E. Karaçam · Jul 14, 2008

PaddyD said:
Hi - thanks for the heads up about the cross post, but you'd be best to try & explain your problem here too - can't be sure that everyone's going to be comfortable navigating to an unknown site...(not to mention that it seems you need to register to view?!?)

Hi PaddyD,

Sorry, but i didn't wanted for a cross post. I am a member of this board like that board. My goal was only learn a new information and help to someone.

Now, i try to tell the problem:

I have a table like below;

<table border=1><tr><td align=left valign=center>Sender's Excel Version.: Office 2003 Professional Turkish Edition / OS Ver.: Windows XP </td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center>+</td><td bgcolor=d4d0c8 width=60>A</td><td bgcolor=d4d0c8 width=60>B</td><td bgcolor=d4d0c8 width=60>C</td><td bgcolor=d4d0c8 width=60>D</td><td bgcolor=d4d0c8 width=60>E</td><td bgcolor=d4d0c8 width=60>F</td><td bgcolor=d4d0c8 width=60>G</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>1</td><td bgcolor=white nowrap=true>Total Dept</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=SUBTOTAL(9;D6:D5000)'>831,05 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>Average Terms</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((SUMPRODUCT(--(D6:D5000*(G6:G5000-TODAY())))/$B$1)+(TODAY()));"";(SUMPRODUCT(--(D6:D5000*(G6:G5000-TODAY())))/$B$1)+(TODAY()))'>10.07.2008 08:26:13.000 (ƒx)</ACRONYM></td><td bgcolor=white></td><td bgcolor=white nowrap=true>A CURRENT ACCOUNT SAMPLE</td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>2</td><td bgcolor=white nowrap=true>Total Credit</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=SUBTOTAL(9;E6:E5000)'>661,05 (ƒx)</ACRONYM></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((SUMPRODUCT(--(E6:E5000*(G6:G5000-TODAY())))/$B$2)+(TODAY()));"";(SUMPRODUCT(--(E6:E5000*(G6:G5000-TODAY())))/$B$2)+(TODAY()))'>31.12.2008 00:00:00.000 (ƒx)</ACRONYM></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>3</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF((B1-B2)=0;"No Remmant";IF((B1-B2)<0;"Remmant Of Credit";"Remmant Of Debt"))'>Remmant Of Debt (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ABS(B1-B2)'>170,00 (ƒx)</ACRONYM></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=ISERROR((DATE(YEAR(TODAY());1;1))+((((((D1-(DATE(YEAR(TODAY());1;1)))*B1)-((D2-(DATE(YEAR(TODAY());1;1)))*B2)))/(B1-B2)+1)))'>05.09.2006 02:44:45.000 (ƒx)</ACRONYM></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>4</td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td><td bgcolor=white></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>5</td><td bgcolor=silver nowrap=true>Document Date</td><td bgcolor=silver nowrap=true>Document Type</td><td bgcolor=silver nowrap=true>Document Saving No</td><td bgcolor=silver nowrap=true>Debt</td><td bgcolor=silver nowrap=true>Credit</td><td bgcolor=silver nowrap=true>Remnant</td><td bgcolor=silver nowrap=true>Procedure Term</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>6</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Invoice</td><td bgcolor=white nowrap=true>DSN 001</td><td bgcolor=white nowrap=true align=right>400</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F5+D6)-E6);(D6-E6);(F5+D6)-E6)'>400,00 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>7.1.2008</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>7</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Bank Check (Cheque)</td><td bgcolor=white nowrap=true>DSN 002</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right>421</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F6+D7)-E7);(D7-E7);(F6+D7)-E7)'>-21,05 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>12.31.2008</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>8</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Invoice</td><td bgcolor=white nowrap=true>DSN 003</td><td bgcolor=white nowrap=true align=right>21</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F7+D8)-E8);(D8-E8);(F7+D8)-E8)'>0,00 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>1.9.2009</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>9</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Invoice</td><td bgcolor=white nowrap=true>DSN 004</td><td bgcolor=white nowrap=true align=right>400</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F8+D9)-E9);(D9-E9);(F8+D9)-E9)'>400,00 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>7.1.2008</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>10</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Bank Check (Cheque)</td><td bgcolor=white nowrap=true>DSN 005</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right>240</td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F9+D10)-E10);(D10-E10);(F9+D10)-E10)'>160,00 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>12.31.2008</td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10>11</td><td bgcolor=white nowrap=true>7.1.2008</td><td bgcolor=white nowrap=true>Invoice</td><td bgcolor=white nowrap=true>DSN 006</td><td bgcolor=white nowrap=true align=right>10</td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><ACRONYM title='=IF(ISERROR((F10+D11)-E11);(D11-E11);(F10+D11)-E11)'>170,00 (ƒx)</ACRONYM></td><td bgcolor=white nowrap=true>7.9.2009</td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center>XL2HTML v12 - 2008</td></tr></table>

I need to learn that how can i edit my formulas in cell: B1, B2, D1, D2 and D3...

My Current Formulas:
B1:
=SUBTOTAL(9,D6:D5000)

B2:
=SUBTOTAL(9,E6:E5000)

D1:
=IF(ISERROR((SUMPRODUCT(--(D6:D5000*(G6:G5000-TODAY())))/$B$1)+(TODAY())),"",(SUMPRODUCT(--(D6:D5000*(G6:G5000-TODAY())))/$B$1)+(TODAY()))

D2:
=IF(ISERROR((SUMPRODUCT(--(E6:E5000*(G6:G5000-TODAY())))/$B$2)+(TODAY())),"",(SUMPRODUCT(--(E6:E5000*(G6:G5000-TODAY())))/$B$2)+(TODAY()))

And,
D3:
=IF(ISERROR((DATE(YEAR(TODAY()),1,1))+((((((D1-(DATE(YEAR(TODAY()),1,1)))*B1)-((D2-(DATE(YEAR(TODAY()),1,1)))*B2)))/(B1-B2)+1))),"",(DATE(YEAR(TODAY()),1,1))+((((((D1-(DATE(YEAR(TODAY()),1,1)))*B1)-((D2-(DATE(YEAR(TODAY()),1,1)))*B2)))/(B1-B2)+1)))

Finally, my question is;

How can i make automaticly; ROW VALUE = 11 INSTEAD OF 5.000 ?

I made an U.D.F. fot this but i want to learn via stored formulas of Excel.

I think that we need an array formula or dynamic Name | Define Solution for this...

Two Examples Below:

The Formula That I Need:
B1: (If the last registered data's row value is 11 )
=SUBTOTAL(9,D6:D11)

D1: (If the last registered data's row value is 255 )
=IF(ISERROR((SUMPRODUCT(--(D6:D255*(G6:G255-TODAY())))/$B$1)+(TODAY())),"",(SUMPRODUCT(--(D6:D255*(G6:G255-TODAY())))/$B$1)+(TODAY()))

And i need these solutions for all my other formulas...

Thanks in advance...

Most Suitable Range Area On Average Term Accounting?

Erdinç E. Karaçam

Board Regular

Excel Facts

PaddyD

MrExcel MVP

Erdinç E. Karaçam

Board Regular

Similar threads

Forum statistics

Share this page

Most Suitable Range Area On Average Term Accounting?

Erdinç E. Karaçam

Board Regular

Excel Facts

PaddyD

MrExcel MVP

Erdinç E. Karaçam

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock