How do I get a formula to change its value and divide by a certain number depending on the text in another column?

[Pulpboi]

I am calculating quarterly expenditures vs monthly and yearly expenditures. I am looking for a formula that will take the total in column AK (2022), and divide the total either quarterly, monthly, or once a year, depending on the text in column AJ. This is a weird one. Basically, I am trying to capitalize expenditures. I was tasked with finding a formula that will divide the total expenditure cost by the text in column AJ. So if it’s quarterly, I would want the total in column AK divided by 4 in 4 different cells. If the text changes, I want the formula to automatically change the values in the columns Al-AQ and maybe give me an error if it changes? The problem I’m running in to is people keep changing the text in AJ, and I can’t catch it because I am doing it manually right now. I can explain more if needed.

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F
1	Unit	Number		Expenses		Quarterly
2	Weekly	52		$50,000		Payments
3	Monthly	12		$51,000		$350,750
4	Quarterly	4		$52,000		$350,750
5	Yearly	1		$53,000		$350,750
6				$54,000		$350,750
7				$55,000
8				$56,000
9				$57,000
10				$58,000
11				$59,000
12				$60,000
13				$61,000
14				$62,000
15				$63,000
16				$64,000
17				$65,000
18				$66,000
19				$67,000
20				$68,000
21				$69,000
22				$70,000
23				$71,000
24				$72,000
25
Sheet7

Cell Formulas
Range	Formula
F3:F6	F3	=ROW(INDIRECT("1:"&VLOOKUP(F1,Table2,2,FALSE)))*0+SUM(Table1[Expenses])/VLOOKUP(F1,Table2,2,FALSE)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F1	List	=$A$2:$A$5

 

[jorismoerings]

Hi and welcome to MrExcel,

Your cell references do not make sense to me. You seem to be saying that some text is being changed in AJ2 but you want the results in Column AI-AQ which includes AJ. In any case we cannot see what the column is or what it would do. That could probably all be resolved, & make it easy for us to use your sample data, with the below information.

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

Providing data and samples in this manner helps us helping you without having to assume or guess what you're trying to accomplish.