Reputation: 374
Excel nested if formulas
I've currently got a Uni project underway and part of this project is an excel spreadsheet which involves inputting data (to be later to be outputted).
Anyway, I don't have too much experience with Excel or programming at all, and I've come across a situation where I need some help.
The project involves building a climbing frame, using EITHER the material Wood, Steel or Aluminium. In the input table, I have added a "Material Quantity" row where a user can select the material they would like to use (from the above list), and then the amount of material they want to use.
The problem is that the materials Steel and Aluminium can ONLY be provided in increments of 6 metres, while Wood can only be provided with an increment of 1 metre.
As you can guess, I want to create something where a user can select their chosen material and then ONLY be able to enter a amount with the same multiple of what it can be provided in. For example, if a user selects "Steel", they can only enter a value with a multiple of 6m - if they enter, for example, 10m, then I want it to return a error.
After some reading, I think the way to solve this problem is to use a nested IF statement with a MOD command too. If anyone can help me with the forumla it would be much appreciated.
The attached picture is what I've acheived so far (and an idea for you guys to see what I'm dealing with). I really have no clue how to approach the problem :(
Upvotes: 1
Views: 235
Answers (2)
Reputation: 254
Another option would be to allow the user to input the exact length of material and then calculate the amount they will need using the 'MROUND' function.
=IF(OR(E8="steel",E8="aluminum"),MROUND(F8,6),IF(E8="wood",MROUND(F8,1),"Not valid"))
Upvotes: 0
Reputation: 35863
Use this formula:
=OR(AND(E8="Wood",MOD(F8,1)=0),AND(OR(E8="Steel",E8="Aluminium"),MOD(F8,6)=0))
Upvotes: 2