Most Suitable Range Area On Average Term Accounting?

Erdinç E. Karaçam

Board Regular
Joined
Sep 23, 2006
Messages
202
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...
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Sep 23, 2006
Messages
202
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><font size=1 color=red face=verdana><b>Sender's Excel Version.:</b></font><font size=1 face=verdana> Office 2003 Professional Turkish Edition / </font><font size=1 color=red face=verdana><b>OS Ver.:</b></font><font size=1 face=verdana> Windows XP </font></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><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>C</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>D</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>E</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>F</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>G</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=green face=verdana><b>Total Dept</b></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=green face=verdana><b><ACRONYM title='=SUBTOTAL(9;D6:D5000)'>831,05 <b>(ƒx)</b></ACRONYM></b></font></td><td bgcolor=white nowrap=true><font size=1 color=green face=verdana><b>Average Terms</b></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=green face=verdana><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 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana><b>A CURRENT ACCOUNT SAMPLE</b></font></td><td bgcolor=white><b></b></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white nowrap=true><font size=1 color=red face=verdana><b>Total Credit</b></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=red face=verdana><b><ACRONYM title='=SUBTOTAL(9;E6:E5000)'>661,05 <b>(ƒx)</b></ACRONYM></b></font></td><td bgcolor=white><b></b></td><td bgcolor=white nowrap=true align=center><font size=1 color=red face=verdana><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 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white><b></b></td><td bgcolor=white><b></b></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=#0066CC face=verdana><b><ACRONYM title='=IF((B1-B2)=0;"No Remmant";IF((B1-B2)<0;"Remmant Of Credit";"Remmant Of Debt"))'>Remmant Of Debt <b>(ƒx)</b></ACRONYM></b></font></td><td bgcolor=white nowrap=true align=center><font size=1 color=#0066CC face=verdana><b><ACRONYM title='=ABS(B1-B2)'>170,00 <b>(ƒx)</b></ACRONYM></b></font></td><td bgcolor=white><b></b></td><td bgcolor=white nowrap=true align=center><font size=1 color=#0066CC face=verdana><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 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white></td><td bgcolor=white><b></b></td><td bgcolor=white><b></b></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></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><font color=black size=1 face=verdana>5</font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Document Date</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Document Type</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Document Saving No</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Debt</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Credit</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Remnant</b></font></td><td bgcolor=silver nowrap=true><font size=1 color=#333399 face=verdana><b>Procedure Term</b></font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>6</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Invoice</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 001</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>400</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F5+D6)-E6);(D6-E6);(F5+D6)-E6)'>400,00 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>7</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Bank Check (Cheque)</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 002</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>421</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F6+D7)-E7);(D7-E7);(F6+D7)-E7)'>-21,05 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>12.31.2008</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>8</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Invoice</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 003</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>21</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F7+D8)-E8);(D8-E8);(F7+D8)-E8)'>0,00 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>1.9.2009</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>9</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Invoice</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 004</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>400</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F8+D9)-E9);(D9-E9);(F8+D9)-E9)'>400,00 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>10</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Bank Check (Cheque)</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 005</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>240</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F9+D10)-E10);(D10-E10);(F9+D10)-E10)'>160,00 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>12.31.2008</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>11</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.1.2008</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Invoice</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>DSN 006</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>10</font></td><td bgcolor=white></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=IF(ISERROR((F10+D11)-E11);(D11-E11);(F10+D11)-E11)'>170,00 <b>(ƒx)</b></ACRONYM></font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>7.9.2009</font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana><b>XL2HTML v12 - 2008</b></font></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...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top