Numbers Formula Help

[RobbieBott]

Hello,

I need some help from the Numbers geniuses... or anyone that may know a bit about numbers formulas.

In Column A I have the numbers of days ranging from 1-4.

In Column B I have two options to select either Full or Half Days.

In Column C I have dollar amounts.

How can I get Column C to autofill a dollar amount based on what is set in columns A & B

So for 4 Full days its $260, for 3 Full Days is $180

Can anyone help me with this?

 

[Cyby-CyberDog]

Replace ‘Full days’ and ‘Half days’ with 1 and 0.5

 

[neutrino17]

You could use an IF statement. Under Help choose Formulas and Functions help. Click Table of Contents and choose Logicals and Informations.

Make four columns.
In column three have an if statement

IF( B1 = “Full”, 1.0, 0.5). This would produce 1.0 for ”Full” and 0.5 for anything else. You could figure out something more detailed to check for ”Half” and exclude errors.

Column 4 multiplies columns 1 and 3.

With a bit of effort you probably could combine the formulas so that you don’t need the intermediate value.


You could add a pop-up menu to column 2 so that only the correct text is entered. Go to Numbers help then search for Controls. In this choose the part on Popup menus.

When formatted, you select a cell then hit the space bar to see the list of options for that cell. Hope this helps.

 

[RobbieBott]

You could use an IF statement. Under Help choose Formulas and Functions help. Click Table of Contents and choose Logicals and Informations.

Make four columns.
In column three have an if statement

IF( B1 = “Full”, 1.0, 0.5). This would produce 1.0 for ”Full” and 0.5 for anything else. You could figure out something more detailed to check for ”Half” and exclude errors.

Column 4 multiplies columns 1 and 3.

With a bit of effort you probably could combine the formulas so that you don’t need the intermediate value.


You could add a pop-up menu to column 2 so that only the correct text is entered. Go to Numbers help then search for Controls. In this choose the part on Popup menus.

When formatted, you select a cell then hit the space bar to see the list of options for that cell. Hope this helps.

This logic gives me the $260 result in Column 3:

IF(AND(A2=4,B2= "Full Days"),260,if-false) I thought I was onto something with this...

but I cannot figure out how to add the second logic to the line to work out the half days, and also the 3,2, & 1 day attenders...

This logic does not work..

IF(AND(A2=4,B2= "Full Days"),260,if-false, IF(AND(A2=4,B2= "Half Days"),180,if-false)

Neither does this:

IF(AND(A2=4,B2= "Full Days"),260,if-false, OR(IF(AND(A2=4,B2= "Half Days"),180,if-false)))

 

[James_C]

Looking at the values in the example, I presume a Full Day rate is 65, and half day rate is 45.

To do in one formula you need to use a Nested IF Statement, the logic first looks to see if the value in Cell B2 is Full Days - it will then set the rate to the True value, 65, if not this value the first IF statement returns the 'False' Value, which is a second nested IF statement. The next IF statement then checks to see if B2 contains Half Days, and will return the value of 45, if not then it will return a value of nil. The formula then multiplies the value returned by the Nested IF Statement by the number of days per week.

The IF function returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.

IF(if-expression, if-true, if-false)

if-expression: A logical expression.

if-true: The value returned if if-expression is TRUE

if-false: An optional argument specifying the value returned if if-expression is FALSE.

The above formula could have been simplified to :

However having only 1 IF Statement only checks that the value in Cell B2 is 'Full Days', if it is not then it will return the FALSE value of 45. The formula would only work if the table was fully completed and Full Days was spelt correctly. If the Value in Column B was left blank or Misspelt then it would return the value of 45, leading to a possible spreadsheet error.

Hope this helps

 

[m..]

There is another option with two tables and VLOOKUP
something like this:

And you can also format the Full-Half cell as a Pop-Up menu if you want to prevent typos.
Its in the Format side panel -> Cell -> Data Format

 

[RobbieBott]

View attachment 2031481

Looking at the values in the example, I presume a Full Day rate is 65, and half day rate is 45.

To do in one formula you need to use a Nested IF Statement, the logic first looks to see if the value in Cell B2 is Full Days - it will then set the rate to the True value, 65, if not this value the first IF statement returns the 'False' Value, which is a second nested IF statement. The next IF statement then checks to see if B2 contains Half Days, and will return the value of 45, if not then it will return a value of nil. The formula then multiplies the value returned by the Nested IF Statement by the number of days per week.

The IF function returns one of two values, depending on whether a specified expression evaluates to a boolean value of TRUE or FALSE.

IF(if-expression, if-true, if-false)

if-expression: A logical expression.

if-true: The value returned if if-expression is TRUE

if-false: An optional argument specifying the value returned if if-expression is FALSE.

The above formula could have been simplified to :

View attachment 2031492

However having only 1 IF Statement only checks that the value in Cell B2 is 'Full Days', if it is not then it will return the FALSE value of 45. The formula would only work if the table was fully completed and Full Days was spelt correctly. If the Value in Column B was left blank or Misspelt then it would return the value of 45, leading to a possible spreadsheet error.

Hope this helps

This works fantastically. I probably should have given more information because the discount complicates things a bit more.

$20 is the current rate for full days however we are giving 3 days free to 4-days weekers, 2 days free to 3-day weekers, and 1 day free to 2-day weekers.

 

[RobbieBott]

There is another option with two tables and VLOOKUP
something like this:
View attachment 2031592
And you can also format the Full-Half cell as a Pop-Up menu if you want to prevent typos.
Its in the Format side panel -> Cell -> Data Format

Could I make this return a static amount?

How could I make this check for both full/half and 1-4 days and then return a static amount based on what it finds?

 

[James_C]

This works fantastically. I probably should have given more information because the discount complicates things a bit more.

$20 is the current rate for full days however we are giving 3 days free to 4-days weekers, 2 days free to 3-day weekers, and 1 day free to 2-day weekers.View attachment 2033692

Hi - I reworked the spreadsheet and added some comments to explain. If there is anything you don't follow then please let me know. I have put a copy of the numbers spreadsheet here if you want to download.

 

[RobbieBott]

Hi - I reworked the spreadsheet and added some comments to explain. If there is anything you don't follow then please let me know. I have put a copy of the numbers spreadsheet here if you want to download.

View attachment 2034051

I haven't been able to work on this over the last few days, but as soon as I get it working, I'll update you. You dear sir and a wiz and numbers!

 

[Slartibart]

Hi - I reworked the spreadsheet and added some comments to explain. If there is anything you don't follow then please let me know. I have put a copy of the numbers spreadsheet here if you want to download.

View attachment 2034051

Excellently done! 👏