Need some help with an IF OR Forumla for my spreadsheet

Abell88

New Member
Joined
Feb 3, 2017
Messages
10
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Greymud

New Member
Joined
Feb 19, 2016
Messages
33
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
=(A1-1)*3+4

Should work for non-zero values of A1.
 

Abell88

New Member
Joined
Feb 3, 2017
Messages
10
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

Well-known Member
Joined
Jan 2, 2009
Messages
1,692
hi

Not sure i understand but this?

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

Dave
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,692

ADVERTISEMENT

or

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

dave
 

Abell88

New Member
Joined
Feb 3, 2017
Messages
10
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

Well-known Member
Joined
Jan 2, 2009
Messages
1,692

ADVERTISEMENT

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

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2017
Messages
10
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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
AB
114
2516
3619
4825
51031
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.
 

Forum statistics

Threads
1,136,313
Messages
5,675,016
Members
419,543
Latest member
Casp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top