My assessment data is styled as the following,
[If male or female], [resulting heartbeat bpm after test], and [age related to expected heartbeat ranges], and answers [below average, average, and above average] accordingly.
I got the vlookup without a problem. It’s the male or female and their separate corresponding tables that I am not getting right.
Would someone kindly write me an example of the formula that may help me. Thanks so much!
Last edited by DollRN; 01-16-2012 at 05:45 AM. Reason: admin left a message and I assumed the person was telling me to change it.
could you upload a small example file without Privileged information >> to do this click Go Advanced -- then click paper clip Attachments --- Add Files select the file to be uploaded click upload files then done
OK, I'll try right now but it may take a little while! Thanks!
OK, Here it is, I hope I did it correctly!
PS. Addendum: Here's the formula that I used:
=IF(I2="female","",AC6(VLOOKUP,AC6,$AD$6:$AE$12,2,"TRUE","",IF(I2="male",AC20,(Vlookup,AC20,$AD$20:$AE$26,2,0))
It comes out wrong and I don't know how to add the "age" into it. Thanks again!
Please read and adhere to these simple rules!
1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.
=IF MALE <--That was the problem but since you left that message, I changed the question to a much more longer suitable question.
grizzly6969, I was able to get it up and you can see all of my mistakes; I'll wait for an answer. Thanks so much...
There seem to be gaps in your table. For example, what would 108bpm be for a 20 y-o male?
Good luck.
If i understood your problem correctly, you would like to extract the heartbeat bpm after test, heartbeat ranges for the particular age and the respective rating of below average, average or above average, right?
Where should these results be populated on your sheet?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
OnErrorGoToO .... that's my excel problem. IDK how to set up the formula. An 18 yrs old should show up as average rating. I am totally new to this excel, currently in excel overload from reading everything, and then getting totally confused by it all. Thank you for your help, I really appreciate it. What do you suggest I do with the age ranges?
I'm a little confused by the workbook you uploaded and by the "formula" you quoted. The formula, as it stands, makes no sense to me.
=IF(I2="female","",AC6(VLOOKUP,AC6,$AD$6:$AE$12,2,"TRUE","",IF(I2="male",AC20,(Vlookup,AC20,$AD$20:$ AE$26,2,0))
Should be more like:
=IF(I2="female",VLOOKUP(AC6,$AD$6:$AE$12,2,False),VLOOKUP(AC20,$AD$20:$AE$26,2,False))
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Arlu1201, yes! The ranges would go on the first sheet (G91) because that's the only sheet I will print out for my patients. It would afford them the opportunity to show their progress to their doctor/s. Thank you so much for your help!
TMShucks,
Thanks so much. I will try that right now! All of the excel people here have been so helpful; you all should get a huge star! I'll let you know how it works out. BRB and again, thank you!
Why?Originally Posted by DollRN
Your average rating for 18-25 year old males is 95-100, but your below average is 102-107, so what about 101? Furthermore, the Poor range is 111-119 so what about 108-110? Where do they fall? If you had complete ranges without gaps, it would be reasonably simple as you merely populate the Age cells with the start age for the range, and the the bpm cells with the start bpm for the range and then lookup the data using a formula like
=IF(I2="Male",INDEX(B8:B14,MATCH(I31,INDEX(C8:H14,0,MATCH(K2,C7:H7,1)),1),1),INDEX(B21:B27,MATCH(I31,INDEX(C21:H27,0,MATCH(K2,C20:H20,1)),1),1))
I have added the changes to the attached version of your file.
Note: if you need to show the ranges in your tables rather than just a start value, you could use separate hidden tables for the lookup data.
OnErrorGoToO,
TMS showed me how to do it and between he, you, and the other posters, I received great instruction and I undertand it now. It's working beautifully! Thanks, again!
You're welcome. Thanks for the rep.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks