Hi, i have problem doing few questions from an excel test given by my lecturer. I am unable to solve a question of number 9 in the Selling sheet. Also, i dont know what caused #DIV/0! error appear in the grey-highlighted Average cells and neither to solve it. Help me please to solve these errors, also kindly explain thoroughly and in detail why the problem appeared and the solutions to fix it. Thank you very much for your attention.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel Math Operation
0
Answers
0
Selected Answer
- Your VLOOKUPs in column B on the Selling sheet look for the value in column A of the source sheet. In all other columns you look for the value in column B. That isn't a good method because it's both confusing and error prone. Always refer to column A in all columns because that is the hard value at the bottom of your system. Adjust the lookup ranges accordingly.
- Selling!F16 probably should be blank. It can't be a monetary value. It can't be a sum. I think K16 is similarly (logically) wrong.
- "If the Achievement DO is equal or greater than 90%, the incentive DO is Rp 100.000. If equal or greater than 100%, the incentive is Rp 125.000"
This is almost a trick question but fair to pose because you will meet this kind of logic often. Here is the logic: A value of 110% should trigger a result of 125K but if you build the formula the way it is described the first part, =IF(110% > 90%, already evaluates to True, and the formula never gets to evalue the False result. Therefore you need to eliminate the larger value first.=IF(110%=>100%,125000,IF(110%>90%,100000,0)) - Excel differentiates between numbers and strings (text). Numbers can be used in calculations, text strings can't. "Rp 125.000" is a string (evidenced by the surrounding quotation marks) and evaluates to either an error or zero, depending upon which function attempts a calculation with it. This is the source of your DIV/0 errors.
Note that "" is a string (text) of zero characters length. It evaluates to an error or zero in exactly the same way as all other strings.
Note that 125.000 has a value of 125, written with 3 decimal places (if the period is your decimal separator). Thousands separators must not be written in cells even by formulas. In your Selling worksheet the cells are formated to display numbers correctly. If you enter 125000 (a number) in a cell like H9 the cell will display Rp 125.000. Check the cell format in Format > Format cells > Numberformat. See the unformated cell value in the Formula Bar. See the formatted cell value in the cell. Use only unformatted numbers in formulas (unless you want to enter a string).
Here is a correct formula.[H9] = IF(F9=>100%,125000,IF(F9>90%,100000,0)) - The AVERAGE function can't process strings, not even null strings ("").
- Replace your existing with this formula.
[S9] = SUM(H9,M9,R9)
The SUM function will evaluate strings as zero. The above function will therefore not show the value you intend but it will not show an error, either.
Observe that R9 contains a string, put there by your formula.
Discussion
So in order to get the right answer in Column R, i have to change the data type in column H, M & R from strings to number? Excel cant do calculation in strings data type?
Fida_mutia (rep: 28) Dec 2, '19 at 9:54 pm
Yes. For example, =IF(K9>100%,"25.000"," ") inserts a string of "25.000" or "". You want to insert the values of 25000 or 0. Therefore the formula should be like =IF(K9>100%,25000,0)
Variatus (rep: 4889) Dec 2, '19 at 11:10 pm
Hey, this may be out of topic from the question I posted but I wanna know how to disable and enable back one of excel functions in the ribbon? For example I wanna disable the pivot table function so that I can't make pivot table, how to disable it and enable it back again?
Fida_mutia (rep: 28) Dec 31, '19 at 1:00 am