Entering an If/Then formula into numbers

Question

Level 1

5 points

Entering an If/Then formula into numbers

Using Numbers, I am trying (valiantly!) to insert a formula that require more than one IF/THEN condition. How would I go about doing this? I have the first part of the formula in but I cannot figure out how to input the rest of the conditions.😟

=IF(Rental 'License type'="1 month";5000;1) is the first of four conditions that need to be checked (1 month, 3 months, 6 months, 12 months) which each have a different value if the condition is true. I understand how to change the current formula to reflect each condition but how would I include all these conditions into the same formula?

Help?😕

iMac, OS X Yosemite (10.10.4)

Posted on Aug 18, 2015 5:17 AM

Reply

Answer 1

Aug 18, 2015 6:11 AM in response to Mineshree

Are you trying to fi all of that into one cell?

I believe that you could fit al this into one cell, but it would be clunky. I would recommend a table that the cell references. For Instance,

	Rent type	Rent amount
Renter 1	1 month	5000
Renter 2	1 year	3500
Renter 3	3 month	4500
Renter 4	etc.	3000

Rent type	Rent amount
1 month	5000
3 month	4500
6 month	4000
1 year	3500
etc.	3000

This way you can reference the second table, and make changes to it easier. The only formula is in column C of table one. Place a formula similar to this one: =INDEX(Table 2::$Rent amount,MATCH($Rent type Renter 1,Table 2::$Rent type,0))

Reply

Answer 2

Yellowbox

Level 7

20,389 points

Aug 18, 2015 6:17 AM in response to Mineshree

Hi Mineshree,

Please reply with a screen shot of a small part of your screen (command shift 4 and drag). Full screen shots (command shift 3) are often difficult to read.

Wayne Contello has written a User Tip on how to post a screen shot.

https://discussions.apple.com/docs/DOC-6591

Also, it will be easier to understand your question if you use cell references such as A1 instead of header names as labels.

Numbers > Menu > Preferences > Untick (uncheck) 'Use Header names as labels'

Regards,

Ian.

Reply

Answer 3

t quinn

Level 5

7,043 points

Aug 18, 2015 7:53 AM in response to Mineshree

Hi Mineshree,

You are describing nested IFs. With 4 conditions I want to encourage you to go with the lookup table charles is suggesting. It has the further advantage of being easy to update when rents change.

As an intellectual exercise, your nested IFs would look something like this:

=IF(C2="1 month",5000,IF(C2="3 month",4500,IF(C2="6 month",4000,IF(C2="1 year+",3500,""))))

Where C2 has your rental license type.

I have in the past used 3 IFs this way but it is awkward. The lookup table can be hidden on another sheet where you only visit when you need to.

quinn

Reply

Answer 4

Mineshree Author

Level 1

5 points

Aug 19, 2015 6:24 AM in response to Mineshree

Thank you all for your help! I managed do all my formulas in my table (except 1) using the info you guys provided me with.

The nested formula that tquinn proposed worked perfectly but I am having one problem with trying to confirm 2 conditions in one nested formula

=IF((G1="Basic"AND(C1=1));0;IF((G1="Basic"AND(C1=0));(44×$I$1);IF((G1="High"AND( C1=1));(22×$I$1);IF((G1="High"AND(C1=0));(88×$I$1);IF(G1=“None";0;0))))))

I want to check if (G1 is a certain string and whether C1 is 1 or 0) to either find a value or continue checking. Somehow, I've lost my mind and I don't know what to use in place of the "AND"

Thank you

Reply