Multiple formulas to calculate a varying number

[Schaeffer]

I am trying to nest a formula that will calculate a number and I will explain as best as possible. For the first 15 days of the month the number will be 4 and for the remainder of the month the number will be 2. I have this worked out as =IF(O6<16,"X","Y")
I would like to have it look at yet another cell to get a variable of the existing number... Confused yet?
=IF(O6<16,"X","Y") and if cell D6 equals 1, X becomes XX. If D6 equals 2, Y becomes YY.
Basically, the end result is a cell that can be a variable of 4 different numbers based on formulas from 2 different cells...

I hope this makes sense.

 

[Joe4]

Welcome to the Board!

Yes, your explanation is a bit confusing.
I think it would be better to explicitly just tell us exactly what ALL the rules are (please do not simplify it on our account, oversimplified explanations often lead to answers that work for your oversimplified examples, but not your real data).
And then show us examples of your data for each scenario along with your expected output.

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Schaeffer]

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	DATE		COUNT		NUMBER		TOTAL			SPLIT
3	1/1/2022		10		1		$63.00		14	$6.30
4	1/30/2022		19		2		$59.85		31	$3.15
5	1/11/2022		15		1		$94.50		14	$6.30
6	1/26/2022		24		2		$75.60		31	$3.15
7
Sheet1

Cell Formulas
Range	Formula
I3:I6	I3	=IF( ISTEXT(A3), 14, IF( DAY(A3)<15,14,DAY(EOMONTH(A3,0))) )
J3:J6	J3	=IF(I3<16,"$6.30","$3.15")
G3:G6	G3	=C3*J3

 

[Schaeffer]

I am trying to get the TOTAL to change based on the NUMBER value. If it's 1 the SPLIT stays the same, if it's 2 the SPLIT changes to say $4.20

Does that make more sense?

 

[Schaeffer]

I don't see an edit function.
I am trying to get the TOTAL to change based on the NUMBER value. If it's 1 the SPLIT stays at either $6.30 or $3.15 as it is. If the NUMBER value is 2 the SPLIT changes to say $4.20 or $2.10.

 

[Joe4]

Try this formula in cell J3 (and copy down):

=IF(I3<16,IF(E3=1,6.3,3.15),IF(E3=1,4.2,2.1))

Note that you do NOT want to include double-quotes in dollar signs in the values you are going to put in column J.
That makes them text, which is not conducive to performing mathematical functions on them.
You should the "$" by applying the currency format to the column, and can elect to show 2 decimals.
Also use cell formatting on numbers if you wish to use them in calculations (as opposed to making them text).

 

[Schaeffer]

It took some working, but I did get it to finally propagate the way that i wanted it to! Thank you! I was wracking my brain for days over that one!

 

[Joe4]

You are welcome.