Excluding N/A in calculation for weighted scores

ChillM94

New Member
Joined
Dec 6, 2020
Messages
2
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
Good day I am building a database for student results. The results entered is a % out of 100. I want to convert it into a score out of its weighted value. Then calculate the average of a semester. As we use N/A or NA where needed, I need a formula that will exclude the text but still include 0% in the calculation as this lowers overall grades.
For the Average calculation I am using formula 1 below that works for both criteria above but I need this formula to now calculate a weighted score per cell.

1. =ROUND(IF(NOT(ISTEXT(E6:H6)),AVERAGE(E6:H6)),5)

I tried calculating the weighted score first but the formula returns #VALUE! if N/A or N/A is an input in the cell.

2. ROUND(IF(NOT(ISTEXT(E5:E6)),(E6*100)*E$5),0)

Any suggestions would be greatly appreciated.
Thanks
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
565
Office Version
  1. 2016
Platform
  1. Windows
If you want to suppress any error, you can use IFERROR

=IFERROR(ROUND(IF(NOT(ISTEXT(E6:H6)),AVERAGE(E6:H6)),5),"")

IF any error including N/A, the result will be just blank "", otherwise the formula is executed.
 
Solution

ChillM94

New Member
Joined
Dec 6, 2020
Messages
2
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
Working perfectly. Thank you very much.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,246
Messages
5,595,044
Members
413,963
Latest member
teggl97

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