Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




23 posts

Geek


# 39840 20-Aug-2009 11:15
Send private message

I need to figure out how to make the "rate" column show the right rate when I type the contractor type into the "contractor" column based on the contractors and rates...

(See pic) https://cdn.geekzone.co.nz/imagessubs/blog1515ff08438b877638b04b190bdd34a0.jpg

Do I use an IF function or a LOOKUP function, can anyone tell me the formula?

This is driving me (more) mental!

PS: I'm a noob here so I'm really sorry if I posted this in the wrong place :(

View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
34 posts

Geek


  # 248972 20-Aug-2009 11:25
Send private message

Try

=IF(B12="","",VLOOKUP(B12,A5:B8,2,FALSE))

I put the If statement in so n/a is not shown when empty otherwise the lookup is fine



23 posts

Geek


  # 248974 20-Aug-2009 11:26
Send private message

Oh you legend! I'll give it a go, thanks so much!! :D

 
 
 
 


19 posts

Geek


  # 248976 20-Aug-2009 11:33
Send private message

or try

=IF(B12=A5,B5,IF(B12=A6,B6,IF(B12=A7,B7,IF(B12=A8,B8," "))))

This will leave the cell blank if there is no contractor



23 posts

Geek


  # 248982 20-Aug-2009 11:41
Send private message

Jimmy that won't work because I can't use A5 to A8 as the references, I have to use the word "Electrician" (A5 is "Electrician:" with a colon), but I'll try replacing your A5 refs as words and see how I go. That looks right :)

nzjwnz - yours didn't work but thanks so much for trying :)

34 posts

Geek


  # 248983 20-Aug-2009 11:44
Send private message

Jimmyoo0: or try

=IF(B12=A5,B5,IF(B12=A6,B6,IF(B12=A7,B7,IF(B12=A8,B8," "))))

This will leave the cell blank if there is no contractor


Yes that would work too but would get a bit longwinded and hard to maintain if more contractor options are added. In the formula i used above it deals with blanks first but you will still get N/A if the contractor value does not match exactly can get around this either by changing 'False' to 'True' and using excel to match or add an IfError e.g.
=IF(ISERROR(VLOOKUP(B12,A5:B8,2,FALSE)),"",VLOOKUP(B12,A5:B8,2,FALSE))

1256 posts

Uber Geek


  # 248985 20-Aug-2009 11:45
Send private message

Given your data as supplied in the image, I would do the following:
1. Remove the colons from the end of the contractor types in column A (otherwise you're going to have to type them in the Contractor column all the time).
2. Make sure your contractor types are sorted in alphabetical order (as they are in the image).
3. In the selected cell (C12 in the image) enter the following formula, making sure you have the correct start and end rows in the formula (rows 5 and 8 respectively in the image):
    =LOOKUP(B12,A$5:A$8,B$5:B$8)
4. Copy this formula to all the cells in the Rate column.
5. Format the cells in Rate to the currency style you're using.

Then, if you entered Painter in B12, C12 would show $25.00.

The "problem" with the formlua in Step 3 is that if the Contractor column is empty, you will get a #N/A error in the Rate column. Personally I hate that, so would do something like this, just to tidy it up:
   =IF(ISBLANK(B12),0,LOOKUP(B12,A$5:A$8,B$5:B$8))
So if the Contractor column is blank, the Rate column will show $0.00 (or $  - depending on your format selection).

&rew



23 posts

Geek


  # 248986 20-Aug-2009 11:45
Send private message

Yus!! It worked :)

=IF(B13="Builder",$B$5,IF(B13="Electrician",$B$6,IF(B13="Painter",$B$7,IF(B13="Plumber",$B$8," "))))

Thanks guys :)

 
 
 
 


34 posts

Geek


  # 248990 20-Aug-2009 11:55
Send private message

Glad you got it to work, i didn't see the colon at the end, changing false to true in my original formula would probably work or you could add it into the lookup value e.g.

=IF(B12="","",VLOOKUP((B12&":"),A5:B8,2,FALSE))

19 posts

Geek


  # 248995 20-Aug-2009 11:58
Send private message

To make it easier to select what contractor you need you could always have a drop down list for cell B12
do this by selecting cell B12 and go to Data>Validation chose list and A5:A8



23 posts

Geek


  # 248996 20-Aug-2009 12:03
Send private message

Yes Jimmy but I can't use A5:A8 because I need it to say "electrician" and not "electrican:" (with colons)...

It's for an exercise at my course that the tutor laid out for me and the tutor is away today, and the workbook aint helping. lol.

1256 posts

Uber Geek


  # 248997 20-Aug-2009 12:11
Send private message

I would go with LOOKUP rather than the nested IF statements. It's simpler, for one thing, but if you think you'll have to add more contractor types in the future (carpet layer, gas fitter, tiler, etc.), then:
- as nzjwnz said, it will get messy maintaining the formula, and
- I think you can only have seven levels of nesting in Excel, which limit the number of types you can have using the IFs



23 posts

Geek


  # 248998 20-Aug-2009 12:13
Send private message

Thanks Andrew. I'm going to lunch. F*ck Excel. Haha

352 posts

Ultimate Geek


  # 249555 21-Aug-2009 21:59
Send private message

Watch out for absolute versus relative references.
=IF(B12="","",VLOOKUP((B12&":"),A5:B8,2,FALSE))

should be
=IF(B12="", "", VLOOKUP((B12 & ":"), $A$5:$B$8, 2, FALSE))

Or else the formula wont for the 2nd row.

3404 posts

Uber Geek

Trusted
Lifetime subscriber

  # 249652 22-Aug-2009 13:42
Send private message

Another way of writing it would be:

=IF(ISERROR(VLOOKUP(B12&":",$A$5:$B$8,1,FALSE)),"",VLOOKUP(B12&":",$A$5:$B$8,2,FALSE))

Apart from the two instances of &":" and the absolute references in the formula, nzjwnz was there ;-)

3404 posts

Uber Geek

Trusted
Lifetime subscriber

  # 249664 22-Aug-2009 14:39
Send private message

Oh yeah - almost forgot to say...named ranges are better to use than absolute references. In your case, you should name the range A5:B9 as something like Trade. Then substitute both instances of $A$5:$B$9 in your formula with the word Trade.

Using a named range like this means that if you expand your listing of trade services, all you will need to do is adjust the named range - your formula won't need touching.

 1 | 2
View this topic in a long page with up to 500 replies per page Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

New AI legaltech product launched in New Zealand
Posted 21-Aug-2019 17:01


Yubico launches first Lightning-compatible security key, the YubiKey 5Ci
Posted 21-Aug-2019 16:46


Disney+ streaming service confirmed launch in New Zealand
Posted 20-Aug-2019 09:29


Industry plan could create a billion dollar interactive games sector
Posted 19-Aug-2019 20:41


Personal cyber insurance a New Zealand first
Posted 19-Aug-2019 20:26


University of Waikato launches space for esports
Posted 19-Aug-2019 20:20


D-Link ANZ expands mydlink ecosystem with new mydlink Mini Wi-Fi Smart Plug
Posted 19-Aug-2019 20:14


Kiwi workers still falling victim to old cyber tricks
Posted 12-Aug-2019 20:47


Lightning Lab GovTech launches 2019 programme
Posted 12-Aug-2019 20:41


Epson launches portable laser projector
Posted 12-Aug-2019 20:27


Huawei launches new distributed HarmonyOS
Posted 12-Aug-2019 20:20


Lenovo introduces single-socket servers for edge and data-intensive workloads
Posted 9-Aug-2019 21:26


The Document Foundation announces LibreOffice 6.3
Posted 9-Aug-2019 16:57


Symantec sell enterprise security assets for US$ 10.7 billion to Broadcom
Posted 9-Aug-2019 16:43


Artificial tongue can distinguish whisky and identify counterfeits
Posted 8-Aug-2019 20:20



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.