Need some help with an IF OR Forumla for my spreadsheet

[Abell88]

I have a somewhat different billing system.

1st User= 4 files minimum
additional Users= 3 file minimum

ex: 2 user= 7 files minimum (4 +3)
3users= 10 files
4users= 13 files

A1 is # of users and A2 column is minimum # of files needed (based on # of users)

I am not sure how to write the formula

= ((A1=1,*4) OR (IF A1><1, (A1-1*3+4))

Please help. Thank you!

 

[Greymud]

=(A1-1)*3+4

Should work for non-zero values of A1.

 

[Abell88]

That will work for all 2 users and above, but a 1 user needs to be = 4 files. So how do I write that IF statement for the 1 users

 

[SQUIDD]

hi

Not sure i understand but this?

=IF(A1=1,A1*4,IF(A1<>1,((A1-1)*3)+4,""))

Dave

 

[SQUIDD]

or

=IF(A1=1,A1*4,B1)

dave

 

[Abell88]

Hi Dave,

is this on the right track the calculation is correct but I am getting a value error? is there suppose to be an OR statement between the 2 IF statements

example 1: COLUMN A1- SHOWS 1 USER COLUMN B1 should calculate the number "4"

A2: Shows 2 users, B2 should calculate the number "7" ( 4 (1st user) + 3 (2nd user) = 7)

A3: Shows 3 users, B3 should calculate the number "10" ( 4 (1st user) + 3 (2nd user) + 3 (3rd user) = 13)

Please advise if you can. Thanks!

 

[SQUIDD]

Hi

=IF(A1=1,4,B1)

Basically if A1 = 1 this will return 4, anything else other than 1 will return value B1.

Is this what you require?

dave

 

[snjpverma]

Hi Dave,

is this on the right track the calculation is correct but I am getting a value error? is there suppose to be an OR statement between the 2 IF statements

example 1: COLUMN A1- SHOWS 1 USER COLUMN B1 should calculate the number "4"

A2: Shows 2 users, B2 should calculate the number "7" ( 4 (1st user) + 3 (2nd user) = 7)

A3: Shows 3 users, B3 should calculate the number "10" ( 4 (1st user) + 3 (2nd user) + 3 (3rd user) = 13)
Please advise if you can. Thanks!

Abell, the formula in post #2 gives a proper output i.e. =(A1-1)*3+4.why don't you try that?

 

[Abell88]

Does not work when I have 200 random number of users in Column A (1,5,6,3,1,7,8,3,6,7, ETC ETC)

The formula needs to be able to look at the cell and if the cell has a 1 in it is will place a "4" in the cell in column B. Then if it reaches 2 the formula should know to bypass the "1" statement and calculate it for 2,3, 4,5 etc users. The # of users are not in order so the formula should read and decide if its a 1 what to do and anything but a 1 and then what to do.

Does this help?

Thanks!

 

[snjpverma]

Does not work when I have 200 random number of users in Column A (1,5,6,3,1,7,8,3,6,7, ETC ETC)

The formula needs to be able to look at the cell and if the cell has a 1 in it is will place a "4" in the cell in column B. Then if it reaches 2 the formula should know to bypass the "1" statement and calculate it for 2,3, 4,5 etc users. The # of users are not in order so the formula should read and decide if its a 1 what to do and anything but a 1 and then what to do.

Does this help?

Thanks!

Excel Workbook
	A	B
1	1	4
2	5	16
3	6	19
4	8	25
5	10	31
Sheet3

I have put random Numbers to let you see the accurate results and the same formula. In case you are getting same result for all your rows then you need to put your calculation Mode on Automatic in Formula > Formula Auditing. As it may be in Manual mode.