Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

need Excel help


Molby

Recommended Posts

ok I know this is completely unprecedented but I need help with spreadsheets

 

I'm doing a rent analysis for a building with lots of small offices in it - 'serviced offices'

 

so

 

column A - lists the rooms 'Room Number'

column B - the asking rents 'Asking Rent'

Column C- a list of Ys or Ns depending on whether the room is rented or not 'Rented?'

column D - the actual rents being received (often smaller than the asking rent due to discounts etc) 'Actual Rent'

column E - this is the bit I can't do: I want the cells in this column to show the rents in column D expressed as percentages of those in column B, i.e. 'actual rent being received for this room as a percentage of the asking rent' BUT, only if it's actually rented, i.e. if it has a Y in the same row in column C; if it's a N then I just want it to stay blank 'Actual Rent as Percentage of Asking'

 

so how do you make a rule, if that's the right word, that if there is a Y in the corresponding row in column C, do the percentage calc in column E?

 

cheers peeps

Link to comment
Share on other sites

Google 'excel if function'

 

It will be something like

If (C="Y", D/B * 100,0)

 

But that is from memory and may be wrong :)

And needs bit more work of course

Edited by AE
Link to comment
Share on other sites

Nah, this is easy. Your formula in E2 would be:

 

=IF(C2="Y",(D2/B2),"")

 

Which translated into English basically says: "If C2 equals Y then put D2/B2 in this cell, otherwise put nothing."

 

Then fill the formula down. Make sure you format column E as a percentage.

 

Or if you want to just leave it as a number then use this formula:

 

=IF(C2="Y",((D2/B2)*100),"")

 

The IF command in Excel works like this:

 

=IF(<criteria>, <value if true>, <value if false>)

 

Your criteria should always be something that returns a true or false, so C2 = "Y", C2<>"N", C2<1, C2>=5 would be examples. So for example:

 

=IF(C2="Y", "C2 has a Y in it", "C2 does not have a Y in it")

 

And you can nest them.

 

=IF(C2="Y", "C2 has a Y in it", IF(C2="N", "C2 has an N in it","C2 has neither a Y or an N in it"))

Edited by philmatthews
Link to comment
Share on other sites

Nah, this is easy. Your formula in E2 would be:

 

=IF(C2="Y",(D2/B2),"")

 

Which translated into English basically says: "If C2 equals Y then put D2/B2 in this cell, otherwise put nothing."

 

Then fill the formula down. Make sure you format column E as a percentage.

 

Or if you want to just leave it as a number then use this formula:

 

=IF(C2="Y",((D2/B2)*100),"")

 

The IF command in Excel works like this:

 

=IF(<criteria>, <value if true>, <value if false>)

 

Your criteria should always be something that returns a true or false, so C2 = "Y", C2<>"N", C2<1, C2>=5 would be examples. So for example:

 

=IF(C2="Y", "C2 has a Y in it", "C2 does not have a Y in it")

 

And you can nest them.

 

=IF(C2="Y", "C2 has a Y in it", IF(C2="N", "C2 has an N in it","C2 has neither a Y or an N in it"))

 

belting

 

 

 

cheers mate, that works great

Link to comment
Share on other sites

Just one thing to be aware of. If someone types something other then Y or N in C2, it will treat it as if it was an 'N'

 

You can get round that by defining N as "-", a blank as a blank and anything other than Y or N as "Incorrect entry"

 

=IF(C2="Y",D2/B2,IF(C2="","",IF(C2="N","-","Incorrect entry"))

 

That way it's easier to tell if it hasn't been either filled in, or if the entry is incorrect.

 

Or you could use data validation I suppose.

But then again I like formulas with loads of "ifs".

 

:)

Link to comment
Share on other sites

if this was on an Apple device you'd just slide the 3D graphic wheel round to the 'Exclude unrented offices" icon (stylized animated GIF of a high tech office with tumbleweed blowing through it), enter your iTunes password twice, agree to the new terms and conditions and the correct result would be instantly displayed - in graphical form*, naturally

 

* to get the actual value download to Excel

Link to comment
Share on other sites

if this was on an Apple device you'd just slide the 3D graphic wheel round to the 'Exclude unrented offices" icon (stylized animated GIF of a high tech office with tumbleweed blowing through it), enter your iTunes password twice, agree to the new terms and conditions and the correct result would be instantly displayed - in graphical form*, naturally

 

* to get the actual value download to Excel

:lol:

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...