• 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.

help required for a formula

S

sdey73

New Member
dear,


i need your help to solve this, 1st year the rent is 50000, it will be same for next two years, on the 4th year it will increase by 15%, and going on. i mean the rent will increase 15% each after three years. please any suggested formula......


regards

sdey73
 
Dear Sdey73,


Please try this =IF(COUNTIF($A$1:A4,A4)=3,A4+($A$1*15%),A4) and please let me know


I have started the value from A1. Please let me know the status


Regards

Vijay
 
@vijay,


thanks for your soon reply, but i need the format like below...


1st year 2nd year 3rd year 4th year 5th year 6th year 7th year 8th year9 th year 10th year

50000 50000 50000 57500 57500 57500 66125 66125 66125 76043.75


regards

sdey73
 
Hi sdey73,


Enter your data into A2:C2, then in D2 put this


=IF(AND(A2=B2,B2=C2),C2+(C2*15%),C2)


Then drag to the right
 
@ oldchippy..


YES.. Yes.. Yes.. I have done it...

Your formula contain.. 36 Character.. and mine.. 35 yeppy...

Code: 
=IF(MOD(COLUMN(A$2),3)=0,A2*115%,A2)


Regards,

Deb
 
@Deb


YES.. Yes.. Yes.. I have done it as well...


'=IF(AND(A2=B2,B2=C2),C2*115%,C2)


32 characters :)


EDIT:


Or possibly


'=IF(A2=C2,C2*115%,C2)


21 characters :-O
 
@oldchippy, Debraj Roy

Hi!

I've got an 8 characters solution, ha ha ha...

Regards!


@SirJB7

Hi, myself, so long...

It seems as this guys on weekends are a little slower than usual... 8 vs. 35/36? or even vs. 22?

Regards!
 
Hi, sdey73!


Following the first part of oldchippy's indications, "Enter your data into A2:C2, then in D2 put this"


Then changing those long and never ending formulas by:

=A2*1.15


Now you can go on with the rest of the guidelines, "Then drag to the right"


Regards!
 
@ OldChippy...


Just one castling...

Enter your data into A2:C2, then in D2 put this
Click to expand...

No need to enter data manually.. and I think check mate..


@ SirJB7..

Who cares the length.. If you have dare.. give a formula which is less than 8 BYTE..

By the way.. I have one.. due to size restriction of WordPress.. I am unable to post it here..


Regards,

Deb
 
@Debraj Roy

Hi!

I didn't set up the rules, I just played the game, talk to oldchippy :)

Regards!

PS: Size matters, don't believe in those who says it doesn't, it's no consolation.
 
@Debraj Roy

Hi!

That's, of course, size matters... but not always in the same direction, my friend.

Regards!

PS: Wrong move.
 
dear all,


i am feeling so happy that a question from me create a competition, you all are genius, relating above matter now i want some more help from you. this time the number of year and the growth percentage to be mentioned in a cell. say the rent for the first year is 50000, i have put the number of year at a prticular cell may be between 1 to 5 and the growth percentage in another cell. then what formula you will suggest. to clear i have attached a link below.....


https://www.dropbox.com/home/rent%20change.xlsx#!/home


regards

sudipta
 
Good day sdey73


The link you have posted is not the public link, go to your public folder in Dropbox and right click on your file and select copy puplic link and then paste into your post
 
sorry,


please go through the following link..


https://www.dropbox.com/s/kmiay7lxqvgd1to/rent%20change.xlsx


regards

sdey73
 
Hi Sudipta,


according to your uploaded file..

In C6 write formula as

Code: 
=IF(MOD(COLUMN(B$6)-1,$B$1)=0,B$6*(1+$B$2),B$6)


Then drag the formula Rightward..


Please let us know the feedback.. :)


Regards,

Deb
 
@Debraj Roy


realy you are genius, thank you very much, the given formula solve my problem, just one help more, wants to know the function of 'mod' or any link i can found it.


regards

sdey73
 
Hi Sudipta,


Dhanyabaad.. :) (Thanks)


Please check below links..

http://chandoo.org/excel-formulas/mod.shtml

http://chandoo.org/forums/topic/formula-to-subtract-time-in-a-specific-format#post-91108


Regards,

Deb
 
Hi, sdey73!


Following oldchippy's philosophy I'll take Debraj Roy's formula:

=IF(MOD(COLUMN(B$6)-1,$B$1)=0,B$6*(1+$B$2),B$6)

and slim it to:

=B$6*IF(MOD(COLUMN(B$6)-1,$B$1),1+$B$2,1)

... half a dozen characters less.


Regards!
 
@SirJB7


sorry to say but i think a little mistake done by you...


your formula is '=B$6*IF(MOD(COLUMN(B$6)-1,$B$1),1+$B$2,1)'

where u have missed "=0"

the formula must be "=B$6*IF(MOD(COLUMN(B$6)-1,$B$1)=0,1+$B$2,1)"

may be i am wrong, if so then sorry..


regards

sdey73
 
Hi everybody,


I'm a novice in Excel. I'm running a shoe store in Bhopal, India and doing my billing in Excel in a simple format I have designed. I'm looking for a solution wherein I can link up my Invoice format to my stock Inventory in 'Table' format.


I have in my Invoice format

column 'A' contains Article code.

After some cols (containing color ,price etc.)

column 'E' contains Quantity.


I have my stock Inventory in a 'Table' by the

side of Invoice on the same page containing

Article codes in col 'S'. In col 'T' I have

current stock quantity in hand against

respective Article Codes.


Now I want after making a fresh Invoice the Quantity in col 'E' of respective Article code should automatically transfer to the new cols by the side of stock Inventory in separate cols 'U', 'V', 'W', 'X' & so on against their respective Article Codes for every fresh Invoice I raise. It will be still better if the respective Invoice no. also automatically appears in the blank header of every new Quantity col 'U', 'V' etc.


For making a fresh Invoice the Article codes are available as required in drop down list in col 'A' of Invoice format linked to col 'S' containing Article Codes in 'Table' of stock Inventory.


I will later formulate to automatically sum up the quantity in a cols 'U' onwards in one separate col and also simultaneously update the stock position in col 'T' being deducted by the quantity in cols 'U', 'V' & so on after every new Invoice I raise. In similar manner the Quantity will be updated by addition of fresh stocks in col ‘T’. I won't mind if you can help me with this too.


I shall be extremely grateful to the visitors of this site for help.

Thanks,

swatantra kumar sethi
 
You must log in or register to reply here.
Back
Top