Can't work out a basic excel formula!!

[Petal]

Hi there!!

I am trying to do a basic excel formula. I have most of it ok but can't work out how to take of a percentage of the total. Here it is:

Weekly rent/7 days*5 days-7.5%

So I want to put in a weekly rent amount and get it back to a daily rent amount then x it by the amount of days vacant minus 7.5% management fee.

I can't work out how to put 7.5% in so am using 7% at the moment!

Any help would be appreciated.

I have attached here what I have done and as you can see it does not work out the correct amount.

 

[tess85]

Firstly, when you put 7.5% in and it shows up as 8%... use the "format cells" command (or Ctrl-1) to add an extra decimal place to your number.

2ndly, your formula should be something like this:
=SUM(B1/7*B2)*(1-b3) as percentages need to be multiplied, not added/subtracted.

 

[tess85]

Actually, I think I replied too quickly, what is the point of the "days vacant" cell? If the property is vacant for 0 days, you get full rent, so the formula should be
=SUM(B1/7*(7-B2))*(1-B3)

 

[Petal]

Thank you Tess, the first formula you gave me worked it out perfectly. I so admire you guys that can do these formulas. I study them and can sometimes work out what is going on, but other times it is just double dutch but the good thing is this one works!!

 

[tess85]

No probs Petal

 

[redwing]

Any idea on how to make an excel cell turn red or *flash* when an expiry date is reached?

Would like to add this into a spreadsheet is possible, have seen it done, but even a search on the net has me stumped

New Year
1. Do Excel Course

 

[LeeF]

It can be done. Look for "conditional formatting" under the format menu.
Say you want to have A1 cell as your expiry date, and the content of B1 cell to turn red when you've passed the expiry date, highlight B1, select conditional formatting and set the properties as you like.
see attached screen dump...

 

[tess85]

you want to use a conditional formula. I can't make it flash but I can make it turn red

Go to Format -> Conditional Formatting and use something like:
"Cell value Is" "Greater than" and input the date you want.

Then press the format button and choose your format.

Now, I haven't used this with dates before, there is a chance you'll need to write your date in number form ie. 1/1/08 in number form is 39448. Does that make sense?

Lol, my job involves Excel so this is my favourite topic

oops - beaten by LeeF!!

 

[BoatBoy]

Any of you guys care to share these fancy new exel spreadsheets here when they are done, please?

http://www.somersoft.com/forums/showthread.php?t=26452

Some of us are seriously exel challenged but really appreciate a good one when they become available.

Dave

 

[Petal]

Any of you guys care to share these fancy new exel spreadsheets here when they are done, please?

http://www.somersoft.com/forums/showthread.php?t=26452

Some of us are seriously exel challenged but really appreciate a good one when they become available.

Dave

Yes I was hoping someone might have a spreadsheet as per this thread

http://www.somersoft.com/forums/showthread.php?t=47735