final excel project

mzdrea215

New Member
Joined
Dec 9, 2012
Messages
5
I have a final excel project that is due in about a week and a half. I will need help with it. i am listing the requirement below and would love suggestions on how to complete it. I am using the idea of a class of 20 students and their quarter 1, 2, 3, and 4 grades along with final exam grades. But that is only about how far I got.

The project is to include:

Using the BITS 211 Project Starter File to record student information, state the

spreadsheet purpose and the type of data on the documentation sheet. The student will

provide information for all users. Hyperlinks will be created to the specific sheets with

cell references to identify the location of each formula/ calculation or component that is

required. It is also mandatory that a student properly explain the purpose of each

component applied in the spreadsheet (i.e. how does this component add value to your

workbook or solve a business question).

A minimum of two worksheets of data (this is the data you will use to begin your

project), properly formatted to enhance the appearance and standardize the overall theme

and look of the project.

A comprehensive compilation of data
to be approved by the instructor (i.e. 25 students in

a class with at least three grade components each with five scores plus an attendance

worksheet, or an inventory of 25 products with at least five columns of data plus an order

worksheet, or an annual budget of 15 categories plus an income statement worksheet)

four formulas or functions (including one IF statement
this can be any type of logical-

IF function excluding IFERROR)

(
Lookup functions are separate from these four formulas.)

a filtered list (to be put on a separate sheet).

conditional formatting (with explanation of purpose on the documentation sheet)

two different types of charts representing different data attractively formatted to show

logical comparisons; grades will be based on readability and sensible choice of chart type

a lookup table with corresponding lookup function

data validation with a corresponding error message

a pivot table with a pivotchart

one of the following items:

one variable data table

goal-seek application

two of the following items:

macro

two variable data table

scenario manager

solver

Required Items

The electronic file, containing all above specifications, must be submitted for assessment

purposes to your instructor at the time of the due date. Please refer to your instructor’s

guidelines for submissions. Electronic review is the best way to confirm accurate formulas,

skills application, and appropriate logical methods.

Scoring

Points are awarded for accuracy, creativity, logical approach, reasonable application, and

adherence to instructions. See the rubric for item by item point values..
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,191
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Unfortunately, your probably going to struggle with assistance on assignments

I'm assuming, you undertook the course and were given notes, previous assignments, etc.
If you refer to those and do some googling, you should be able to complete the task.
By all means, if you almost get completed and get stuck, I'm sure someone here will offer advice.
But you will have to had a significant input first.
If we help get this project through...who gets the credits ??
 

mzdrea215

New Member
Joined
Dec 9, 2012
Messages
5
I have started my project and am having trouble with the lookup function. Below I have copied and pasted the start of my project and I need to use a lookup function to transfer the letter grade to the student's average grade. I have tried a normal lookup and it works for some of the grades and in other ones it shows #n/a. Please Help!!!

Student ID</SPAN>Quarter 1 Test</SPAN>Quarter 2 Test</SPAN>Quarter 3 Test</SPAN>Quarter 4 Test</SPAN>Final Exam</SPAN>Average</SPAN>
1</SPAN>84</SPAN>89</SPAN>95</SPAN>92</SPAN>98</SPAN>92</SPAN>A-</SPAN>
2</SPAN>72</SPAN>77</SPAN>83</SPAN>71</SPAN>82</SPAN>77</SPAN>C+</SPAN>
3</SPAN>68</SPAN>72</SPAN>79</SPAN>81</SPAN>84</SPAN>77</SPAN>C</SPAN>
4</SPAN>54</SPAN>69</SPAN>71</SPAN>75</SPAN>77</SPAN>69</SPAN>D+</SPAN>
5</SPAN>78</SPAN>43</SPAN>86</SPAN>92</SPAN>88</SPAN>77</SPAN>C+</SPAN>
6</SPAN>55</SPAN>64</SPAN>72</SPAN>81</SPAN>99</SPAN>74</SPAN>C</SPAN>
7</SPAN>95</SPAN>98</SPAN>97</SPAN>82</SPAN>100</SPAN>94</SPAN>A-</SPAN>
8</SPAN>88</SPAN>84</SPAN>83</SPAN>81</SPAN>87</SPAN>85</SPAN>B</SPAN>
9</SPAN>59</SPAN>62</SPAN>57</SPAN>87</SPAN>72</SPAN>67</SPAN>#N/A</SPAN>
10</SPAN>72</SPAN>70</SPAN>77</SPAN>78</SPAN>81</SPAN>76</SPAN>#N/A</SPAN>
11</SPAN>85</SPAN>87</SPAN>92</SPAN>94</SPAN>98</SPAN>91</SPAN>A-</SPAN>
12</SPAN>69</SPAN>72</SPAN>73</SPAN>79</SPAN>81</SPAN>75</SPAN>#N/A</SPAN>
13</SPAN>82</SPAN>78</SPAN>86</SPAN>88</SPAN>90</SPAN>85</SPAN>#N/A</SPAN>
14</SPAN>73</SPAN>75</SPAN>69</SPAN>88</SPAN>91</SPAN>79</SPAN>#N/A</SPAN>
15</SPAN>95</SPAN>94</SPAN>88</SPAN>86</SPAN>92</SPAN>91</SPAN>#N/A</SPAN>
16</SPAN>77</SPAN>81</SPAN>73</SPAN>89</SPAN>78</SPAN>80</SPAN>#N/A</SPAN>
17</SPAN>93</SPAN>97</SPAN>87</SPAN>79</SPAN>92</SPAN>90</SPAN>#N/A</SPAN>
18</SPAN>59</SPAN>63</SPAN>65</SPAN>72</SPAN>73</SPAN>66</SPAN>#N/A</SPAN>
19</SPAN>100</SPAN>98</SPAN>100</SPAN>96</SPAN>98</SPAN>98</SPAN>#N/A</SPAN>
20</SPAN>89</SPAN>91</SPAN>93</SPAN>77</SPAN>85</SPAN>87</SPAN>#N/A</SPAN>
Grade Scale</SPAN>
Points</SPAN>Letter Grade </SPAN>
0</SPAN>F</SPAN>
60</SPAN>D-</SPAN>
64</SPAN>D</SPAN>
67</SPAN>D+</SPAN>
70</SPAN>C-</SPAN>
74</SPAN>C</SPAN>
77</SPAN>C+</SPAN>
80</SPAN>B-</SPAN>
84</SPAN>B</SPAN>
87</SPAN>B+</SPAN>
90</SPAN>A-</SPAN>
95</SPAN>A</SPAN>
100</SPAN>A+</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,191
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Post the formula you are using.
 

mzdrea215

New Member
Joined
Dec 9, 2012
Messages
5
the formula I am using is =VLOOKUP(G2,A23:B35,2,TRUE)

Here are the results:

A-
C+
C
D+
C+
C
A-
B
#N/A
#N/A
A-
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

<COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 4053" width=95><TBODY>
</TBODY>
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,191
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Your problem is because you are using Relative references, not absolute references
Try this and note the $ signs in the formula
Code:
=VLOOKUP(G2,$A$23:$B$35,2,TRUE)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,212
Messages
5,640,895
Members
417,177
Latest member
njosh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top